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!

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Heliocracy" wrote in message
...
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!

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


Replace @mailinator.com with @tiscali.co.uk


"Heliocracy" wrote in message
...
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!

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" wrote in message
...
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!

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

"Peo Sjoblom" wrote:

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" wrote in message
...
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!

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


"Heliocracy" wrote in message
...
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

"Peo Sjoblom" wrote:

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" wrote in message
...
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!