Need advanced SUMIF
 

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

Need advanced SUMIF
 

Best to help yourself by searching google

sumproduct:excel

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Welshr2" wrote in message
...
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

Need advanced SUMIF
 

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


"Welshr2" wrote:

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

Need advanced SUMIF
 

Check out this link...

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
--
HTH...

Jim Thomlinson


"Welshr2" wrote:

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

Need advanced SUMIF
 

On Jan 5, 4:06*pm, Welshr2 wrote:
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

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