SUMPRODUCT formula doesn't work!

[Heliocracy]

I need to count the number of rows in which:

1. The date in column B falls within October 2007
2. Column G contains "MK"
3. Column O contains "SD"
4. Column Q contains "Stage"

I've tried this formula:
=SUMPRODUCT(--(TEXT('[Combined ESQ Notes.xls]Combined
Notes'!$B:$B,"yyyymm")="200710")*('[Combined ESQ Notes.xls]Combined
Notes'!$G:$G="MK")*('[Combined ESQ Notes.xls]Combined
Notes'!$O:$O="SD")*('[Combined ESQ Notes.xls]Combined Notes'!$Q:$Q="Show"))

....and it gives me a #NUM! error. Can anyone tell me why, or suggest a
correction?

Thanks in advance for any help you can give me!

 

[Don Guillett]

try
year(rng)=2007
month(rng)=10

 

[Sandy Mann]

SUMPRODUCT() cannot work with whole columns, try using ranges like:

B1:B65535 or B2:B65536

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Peo Sjoblom]

You can't use the whole range, i.e. B:B etc

use

B1:B65535

or better something less unless you really have plus 65000 rows but in that
case I suspect that formula would be rather slow


--


Regards,


Peo Sjoblom

 

[Heliocracy]

Okay I put real ranges in for each of the four columns (the worksheet being
counted does contain 65536 records), and now it gives me an #N/A error. When
I do a manual count, I find there are nine records that fit the criteria--so
this formula should return a 9. Any other ideas?

Thanks,
Mike

You can't use the whole range, i.e. B:B etc

use

B1:B65535

or better something less unless you really have plus 65000 rows but in that
case I suspect that formula would be rather slow


--


Regards,


Peo Sjoblom

I need to count the number of rows in which:

1. The date in column B falls within October 2007
2. Column G contains "MK"
3. Column O contains "SD"
4. Column Q contains "Stage"

I've tried this formula:
=SUMPRODUCT(--(TEXT('[Combined ESQ Notes.xls]Combined
Notes'!$B:$B,"yyyymm")="200710")*('[Combined ESQ Notes.xls]Combined
Notes'!$G:$G="MK")*('[Combined ESQ Notes.xls]Combined
Notes'!$O:$O="SD")*('[Combined ESQ Notes.xls]Combined
Notes'!$Q:$Q="Show"))

...and it gives me a #NUM! error. Can anyone tell me why, or suggest a
correction?

Thanks in advance for any help you can give me!

 

[Peo Sjoblom]

You will get that error if your ranges are different in size
or if the range itself contains a N/A error
--


Regards,


Peo Sjoblom

Okay I put real ranges in for each of the four columns (the worksheet
being
counted does contain 65536 records), and now it gives me an #N/A error.
When
I do a manual count, I find there are nine records that fit the
criteria--so
this formula should return a 9. Any other ideas?

Thanks,
Mike

You can't use the whole range, i.e. B:B etc

use

B1:B65535

or better something less unless you really have plus 65000 rows but in
that
case I suspect that formula would be rather slow


--


Regards,


Peo Sjoblom

I need to count the number of rows in which:

1. The date in column B falls within October 2007
2. Column G contains "MK"
3. Column O contains "SD"
4. Column Q contains "Stage"

I've tried this formula:
=SUMPRODUCT(--(TEXT('[Combined ESQ Notes.xls]Combined
Notes'!$B:$B,"yyyymm")="200710")*('[Combined ESQ Notes.xls]Combined
Notes'!$G:$G="MK")*('[Combined ESQ Notes.xls]Combined
Notes'!$O:$O="SD")*('[Combined ESQ Notes.xls]Combined
Notes'!$Q:$Q="Show"))

...and it gives me a #NUM! error. Can anyone tell me why, or suggest a
correction?

Thanks in advance for any help you can give me!