Counting rows with two arguments

[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!

 

[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!

 

[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!

 

[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!

 

[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?

 

[emailbox2006-monical]

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

 

[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?

 

[Dave Peterson]

Glad you got it working.

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