sumproduct with two criteria on one column??

  • Thread starter Thread starter Johnny B
  • Start date Start date
J

Johnny B

I have Window Vista, Office, Excel

Col A contains Mon or Tue or Wed or etc days of the weel, Col B
contains the date Jan 1, Jan 2, etc, Col C contains a dollar amount
for each day. I want to count how many Tue, Wed, Thu, etc there are
that have dollar amounts not 0 or blank cells. Each cell in Col C has
a formula that calculates the dollar amount from another page of the
work book.

I cannot get sumproduct to calculate this with two criteria on Col C,
if the cell is <0 not to coune it and to sum the cells with >0
amounts.

Any thoughts.

Thank you,
Jerry
 
=SUMPRODUCT(--(A1:A100="Mon"),--(C1:C100<>0))
Tell us if this works
best wishes
 
Hi,

I think what you want is

=SUMPRODUCT(--(A1:A100="Mon"),--(C1:C100>0),--(C1:C100<>""))

because you say there are formulas in column C which may evaluate to blank.
But if they all evaluate to numbers then

=SUMPRODUCT(--(A1:A100="Mon"),--(C1:C100>0))

If this helps, please click the Yes button

Cheers,
Shane Devenshire
No need for two criteria on column C.
 
Hi,
Thanks, Shane's formula worked but I could not get Bernard's to get
the correct number. I do thank you both for your help. J
 
Back
Top