Sum If with two ranges and condition

[Guest]

Hy

I have to sum the values from A1:A100 if B1:B100="01-01-2006" and C1:C100="1"

With only one it works like this sumif(B1:B100;"01-01-2006"; A1:A100), but I
want some thing like this sumif(B1:B100;"01-01-2006"AND(C1:C100;"1"); A1:A100)

Any help please

 

[JE McGimpsey]

One way:

=SUMPRODUCT(--(B1:B100=DATE(2006,1,1)),--(C1:C100=1),A1:A100)

For an explanation of the "--" usage, see

http://www.mcgimpsey.com/excel/doubleneg.html

 

[Guest]

Hi !
It don't work ! I know that exist a way to do it ! summing with to criteria:
Sum A1:A100
If B1:B100 = "text" and C1:C100 = "text1"

Thanks any way

"JE McGimpsey" escreveu:

 

[JE McGimpsey]

If both criteria fields are text:

=SUMPRODUCT(--(B1:B100="01-01-2006");--(C1:C100="1");A1:A100)

 

[Guest]

Hi again,

What I'm doing wrong ?

=SOMA.SE(Plano!$C$9lano!$C$724;"01-12-2004";Plano!$L$9lano!$L$724)
This is OK.
when I try your sugestion to put one more criteria it don't work the cell
stay blank with the formula inside (no error)

=SOMARPRODUTO(--(Plano!$C$9lano!$C$724="01-12-2004");--(Plano!$P$9lano!$P$724="1");Plano!$L$9lano!$L$724)

Thanks in advance



"JE McGimpsey" escreveu:

 

[Guest]

Thanks, with your indications I could solve the problem like this:

=SOMARPRODUTO(--(Plano!$C$9lano!$C$724=DATA(2004;12;1));--(Plano!$P$9lano!$P$724=1);Plano!$L$9lano!$L$724)

"JE McGimpsey" escreveu:

 

[RobinMass]

Thank you JE. This worked great!

Thanks, with your indications I could solve the problem like this:

=SOMARPRODUTO(--(Plano!$C$9lano!$C$724=DATA(2004;12;1));--(Plano!$P$9lano!$P$724=1);Plano!$L$9lano!$L$724)

"JE McGimpsey" escreveu: