Search code examples
excelexcel-formulacountifsumifs

calculating values in excel using sumproduct

I've tried sumproduct and i've tried sumif but I think what I need is a combination (or a better understanding of sumproduct)

Here is my data

state | percent

NSW | 0

NSW | 20

VIC | 0

SA | 0

WA | 15

NSW | 0

NSW | 70

What I want to try and calculate is as follows:

  • Where state = NSW

AND

  • Where percent > 0

I want to work out the average of the matched values

By including values = 0 I can use:

=SUMIF(A:A,"NSW",B:B)/COUNTIF(A:A,"NSW")

But now I want further define by removing the 0 values.

Thanks

Solution

  • You could use this

    criteria total/criteria count approach

    assumes your dataset is in A1:B7, please update as necessary

    All Excel versions

    =SUMPRODUCT(--(A1:A7="NSW"),--(B1:B7>0),B1:B7)/SUMPRODUCT(--(A1:A7="NSW"),--(B1:B7>0))

    Excel 07/10 only

    =SUMIFS(B1:B7,B1:B7,">0",A1:A7,"NSW")/COUNTIFS(B1:B7,">0",A1:A7,"NSW")