Sum If with two ranges and condition
 

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

Sum If with two ranges and condition
 

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

In article ,
Nuno Jácome wrote:

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

Sum If with two ranges and condition
 

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:

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

In article ,
Nuno Jácome wrote:

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

Sum If with two ranges and condition
 

If both criteria fields are text:

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


In article ,
Nuno Jácome wrote:

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:

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

In article ,
Nuno Jácome wrote:

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

Sum If with two ranges and condition
 

Hi again,

What I'm doing wrong ?

=SOMA.SE(Plano!$C$9:Plano!$C$724;"01-12-2004";Plano!$L$9:Plano!$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$9:Plano!$C$724="01-12-2004");--(Plano!$P$9:Plano!$P$724="1");Plano!$L$9:Plano!$L$ 724)

Thanks in advance



"JE McGimpsey" escreveu:

If both criteria fields are text:

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


In article ,
Nuno Jácome wrote:

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:

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

In article ,
Nuno Jácome wrote:

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

Sum If with two ranges and condition
 

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

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

"JE McGimpsey" escreveu:

If both criteria fields are text:

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


In article ,
Nuno Jácome wrote:

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:

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

In article ,
Nuno Jácome wrote:

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

Sum If with two ranges and condition
 

Thank you JE. This worked great!

"Nuno Jácome" wrote:

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

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

"JE McGimpsey" escreveu:

If both criteria fields are text:

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


In article ,
Nuno Jácome wrote:

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:

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

In article ,
Nuno Jácome wrote:

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