Sumproduct VS SUMIF VS CountIF

[TeresaMarx]

I am creating a report and all was going well untill I hit this snaffu, I need a formula that will only count the rows in column c between 7/1/11 and 7/31/11 and only if the rows in column D start with x. So far this is what I have and it is not working


=SUMPRODUCT((Declines!C:C>="7/1/2011"+0)*(Declines!C:C<="7/31/2011"+0)*(Declines!B:B="X*"))

 

[VoG]

Try like this

=SUMPRODUCT(--(Declines!C1:C100>="7/1/2011"+0),--(Declines!C1:C100<="7/31/2011"+0)--(LEFT(Declines!B1:B100,1)="X"))

 

[T. Valko]

I am creating a report and all was going well untill I hit this snaffu, I need a formula that will only count the rows in column c between 7/1/11 and 7/31/11 and only if the rows in column D start with x. So far this is what I have and it is not working


=SUMPRODUCT((Declines!C:C>="7/1/2011"+0)*(Declines!C:C<="7/31/2011"+0)*(Declines!B:B="X*"))

You must be using Excel 2007 or later.

Try this...

Use cells to hold the date criteria:

• X1 = 7/1/2011
• X2 = 7/31/2011

Then:

=COUNTIFS(Declines!C:C,">="&X1,Declines!C:C,"<="&X2,Declines!B:B,"X*")

 

[TeresaMarx]

You must be using Excel 2007 or later.

Try this...


Use cells to hold the date criteria:

• X1 = 7/1/2011
• X2 = 7/31/2011

Then:

=COUNTIFS(Declines!C:C,">="&X1,Declines!C:C,"<="&X2,Declines!B:B,"X*")

Worked like magic Thank you so much!!!!

 

[T. Valko]

Worked like magic Thank you so much!!!!

You're welcome. Thanks for the feedback!