"If And If then sum" formula

[SHA_doms]

Does anybody have a suggestion to how it is possible to test two criterias (in complete columns) and return the sum of all cells meeting both criterias?

FX: If(A:A=D24) And If(B:B=C24) Then sum


appreciate the effort
Søren

[NBVC]

What range is being summed?

[SHA_doms]

Sorry..

FX: If(A:A=D24) And If(B:B=C24) Then sum C:C

[NBVC]

Try:

=Sumproduct((A1:A100=D24)*(B1:B100=C24),C1:C100)

adjust ranges to suit.

Note: You can't use whole column references with Sumproduct unless you use XL2007

[SHA_doms]

Works like a charm

Cheers mate

[madmanmac]

I tried this just to learn how it works and I get a "zero" as my answer. I'm not sure it is reading the "=D24" and "=c24" components correctly. Any thoughts?

[NBVC]

The formula looks in range A1:A100 looking for matches to whatever is in D24 and it looks in range B1:B100 for matches to what's in C24.

Where there are matches in both columns (in the same rows), it will sum up values in column C1:C100...

So what you enter in C24 and D24 is what you want to look for in A1:100 and B1:B100... the matches have to next to each other...

[madmanmac]

The formula looks in range A1:A100 looking for matches to whatever is in D24 and it looks in range B1:B100 for matches to what's in C24.

Where there are matches in both columns (in the same rows), it will sum up values in column C1:C100...

So what you enter in C24 and D24 is what you want to look for in A1:100 and B1:B100... the matches have to next to each other...

I found my mistake --- it was something pretty simple and stupid..............