|
SUMPRODUCT formula doesn't work!
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 formula doesn't work!
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! |
SUMPRODUCT formula doesn't work!
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! |
SUMPRODUCT formula doesn't work!
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! |
SUMPRODUCT formula doesn't work!
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! |
| All times are GMT +1. The time now is 12:38 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com