Need advanced SUMIF

W

Welshr2

I have 11 columns of data and would normally use sumif to add up the values
based on the doc type added, however now the VAT rate has changed I need to
identify 2 critera in 2 columns and sumif wont allow that.

Any help would be greatfully accepted
 
S

Sheeloo

You can use SUMPRODUCT

=SUMPRODUCT(--(A1:A100="cond1"),--(B1:B100="cond2"),(C1:C100))

This will sum values in Col C for those rows where Col A has the value cond1
and Col B has the value cond2
 
J

jlclyde

I have 11 columns of data and would normally use sumif to add up the values
based on the doc type added, however now the VAT rate has changed I need to
identify 2 critera in 2 columns and sumif wont allow that.

Any help would be greatfully accepted
Click to expand...

To elaborate a little more you can use Sumproduct like this
=SUMPRODUCT((A1:A10=5)*(B1:B10=9)*1). This would count if both
conditions were met in A and B. Here is how to add C if A and B are
met. =SUMPRODUCT((A1:A10=5)*(B1:B10=9)*(C1:C10)). Always make sure
that your arrays are the same size, like 1:10...

Jay
 
Top