2

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

Community
  • 1
  • 1
php-b-grader
  • 3,191
  • 11
  • 42
  • 53

2 Answers2

2

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")

brettdj
  • 54,857
  • 16
  • 114
  • 177
  • I've always used multiplication in my Sumproduct formulas, and until just now hadn't realized that the " ,B1:B7" part avoids the #VALUE error I'd get by using " * B1:B7". I've always worked around it by not including the header in Sumproduct, I knew about using commas instead, just never realized this benefit. +2! – Doug Glancy Jan 11 '12 at 04:24
  • @DOugGlancy thx Doug, it is the coercion of the TRUE / FALSe values to 1 / 0 respectively by use of the double negative `--` that allows the standard SUMPRODUCT construct of `,` – brettdj Jan 11 '12 at 04:38
  • I actually tried the sumproduct but had the syntax wrong. Working perfectly now - thanks – php-b-grader Jan 11 '12 at 04:43
  • I thought that, butSUMPRODUCT((A1:A8="NSW ")*(B1:B8>0),B1:B8)/SUMPRODUCT((A1:A8="NSW ")*(B1:B8>0)) works, whereas =SUMPRODUCT((A1:A8="NSW ")*(B1:B8>0)*B1:B8)/SUMPRODUCT((A1:A8="NSW ")*(B1:B8>0)) yields #VALUE. The only thing different is the one comma. – Doug Glancy Jan 11 '12 at 04:48
  • @DougGlancy Yep, quite right. That is because the `(A1:A8="NSW ")*(B1:B8>0)` component has been coerced by the multiplication so it can be combined with a "," with `B1:B7` as you did. Whereas this fails `=SUMPRODUCT((A1:A7="NSW"),(B1:B7>0),B1:B7)/SUMPRODUCT((A1:A7="NSW"),(B1:B7>0))` – brettdj Jan 11 '12 at 04:54
  • I understand the coercion part, and have always preferred "*", but what I realize I don't understand is what the comma does, and why it doesn't yield a #VALUE error. I bet you do though. – Doug Glancy Jan 11 '12 at 05:07
  • 1
    Hello Doug. If you use the `"native"` SUMPRODUCT syntax (i.e. with commas) then any text is ignored so while this gives an error -=SUMPRODUCT({"x",2}*{1,3}) because the multiplication of the arrays happens before SUMPRODUCT calculates anything....and you can't multiply by "x" - this formula returns 6, =SUMPRODUCT({"x",2},{1,3}) because "x" is ignored (effectively treated like zero) – barry houdini Jan 11 '12 at 16:54
  • @barryhoudini thanks. That makes sense. SO makes me smarter every day! – Doug Glancy Jan 11 '12 at 18:07
  • @DougGlancy Barry is without peer when it comes to Excel formulae - I just sit back and marvel. – brettdj Jan 12 '12 at 01:44
0

In Excel 2007 and later you can use AVERAGEIFS

=AVERAGEIFS(B1:B7,B1:B7,">0",A1:A7,"NSW")

barry houdini
  • 45,615
  • 8
  • 63
  • 81