CODEWITHSUNDEEP
excelexcel-formulacountifsumifs
php-b-grader
php-b-grader

Reputation: 3325

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:

AND

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

Upvotes: 2

Views: 1069

Answers (2)

barry houdini
barry houdini

Reputation: 46411

In Excel 2007 and later you can use AVERAGEIFS

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

Upvotes: 0

brettdj
brettdj

Reputation: 55702

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

Upvotes: 2

Related Questions