Counting rows with two arguments

  • Thread starter emailbox2006-monical
  • Start date
E

emailbox2006-monical

Here is what I've come up with but it doesn't work:

=SUMPRODUCT(
([sold.xls]main!$X:$X="ccc")*([sold.xls]main!$ac:$ac="9/23/06"))

I need to count the number of rows that have
1) the letters "ccc" in column X and
2) the date in column AC

Please help!
 
D

Dave Peterson

Don't use the whole column. And make sure you compare dates with dates--not
strings.


=SUMPRODUCT(([sold.xls]main!$X1:$X999="ccc")
*([sold.xls]main!$ac1:$ac999=date(2006,9,23)))



Here is what I've come up with but it doesn't work:

=SUMPRODUCT(
([sold.xls]main!$X:$X="ccc")*([sold.xls]main!$ac:$ac="9/23/06"))

I need to count the number of rows that have
1) the letters "ccc" in column X and
2) the date in column AC

Please help!
Click to expand...
 
E

emailbox2006-monical

Ok, so I tried:

=SUMPRODUCT(([sold.xls]main!$X100:$X9999="ccc")*([sold.xls]main!$AC100:$AC9999=DATE(2006,9,23)))

but I'm getting #N/A as the result. I know we're close!
 
D

Dave Peterson

Do you have any errors in any of those ranges?

If you do, then clean those errors up.

Ok, so I tried:

=SUMPRODUCT(([sold.xls]main!$X100:$X9999="ccc")*([sold.xls]main!$AC100:$AC9999=DATE(2006,9,23)))

but I'm getting #N/A as the result. I know we're close!
Click to expand...
 
E

emailbox2006-monical

Possibly, so just to get it working, I narrowed it down to rows with no
errors and went back into the same spreadsheet where all the data is...
So I tried:

=SUM((X7061:X7063="ccc")*(AC7061:AC7063=DATE(2006,9,23)))

and now I get #value! as the result. It's driving me mad cause I've
tried a gazillion different ways. Any thoughts?
 
E

emailbox2006-monical

Ack! I had forgotten to use "SUMPRODUCT". It's working great now!!
Thanks!!
 
D

Dave Peterson

=sumproduct()

Possibly, so just to get it working, I narrowed it down to rows with no
errors and went back into the same spreadsheet where all the data is...
So I tried:

=SUM((X7061:X7063="ccc")*(AC7061:AC7063=DATE(2006,9,23)))

and now I get #value! as the result. It's driving me mad cause I've
tried a gazillion different ways. Any thoughts?
Click to expand...
 
Top