Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count the times a value appears in a column based on another value
I am using the following formula to count the number of times "Web" appears
in column B based on "1" being in column D: =IF('List of Reviews'!B:B="Web",COUNTIF('List of Reviews'!D:D,1),0) I continue to get a #NUM! error for the logical test in the IF statement even when the formula is reversed and I try to count the number of times "Web" appears for every row which contains a "1" in column D. What is wrong with the formula? Is there another function that will return the correct result? Just a note: The COUNTIF function works each time to find the right value for the criteria entered. Help needed quickly! Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count the times a value appears in a column based on anothervalue
Try this:
=SUMPRODUCT(('List of Reviews'!B1:B1000="Web")*('List of Reviews'! D1:D1000=1)) Adjust the ranges to suit your data, but you can't use full-column references (unless you are using XL2007, in which case you could probably use COUNTIFS). Hope this helps. Pete On Dec 31, 12:45*am, TrainingGuru wrote: I am using the following formula to count the number of times "Web" appears in column B based on "1" being in column D: =IF('List of Reviews'!B:B="Web",COUNTIF('List of Reviews'!D:D,1),0) I continue to get a #NUM! error for the logical test in the IF statement even when the formula is reversed and I try to count the number of times "Web" appears for every row which contains a "1" in column D. What is wrong with the formula? Is there another function that will return the correct result? Just a note: The COUNTIF function works each time to find the right value for the criteria entered. Help needed quickly! Thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count the times a value appears in a column based on another v
Thank you, Pete. That did the trick. I tried to use SUMPRODUCT and even a
form of an array before but wasn't successful. I don't yet understand how this function works. I will need to do more research. Can you suggest a resource? Your response saved the day (actually saved my week!). Thank you again. "Pete_UK" wrote: Try this: =SUMPRODUCT(('List of Reviews'!B1:B1000="Web")*('List of Reviews'! D1:D1000=1)) Adjust the ranges to suit your data, but you can't use full-column references (unless you are using XL2007, in which case you could probably use COUNTIFS). Hope this helps. Pete On Dec 31, 12:45 am, TrainingGuru wrote: I am using the following formula to count the number of times "Web" appears in column B based on "1" being in column D: =IF('List of Reviews'!B:B="Web",COUNTIF('List of Reviews'!D:D,1),0) I continue to get a #NUM! error for the logical test in the IF statement even when the formula is reversed and I try to count the number of times "Web" appears for every row which contains a "1" in column D. What is wrong with the formula? Is there another function that will return the correct result? Just a note: The COUNTIF function works each time to find the right value for the criteria entered. Help needed quickly! Thanks. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count the times a value appears in a column based on another v
Everything you always wanted know about SUMPRODUCT (and then some!):
http://xldynamic.com/source/xld.SUMPRODUCT.html -- Biff Microsoft Excel MVP "TrainingGuru" wrote in message ... Thank you, Pete. That did the trick. I tried to use SUMPRODUCT and even a form of an array before but wasn't successful. I don't yet understand how this function works. I will need to do more research. Can you suggest a resource? Your response saved the day (actually saved my week!). Thank you again. "Pete_UK" wrote: Try this: =SUMPRODUCT(('List of Reviews'!B1:B1000="Web")*('List of Reviews'! D1:D1000=1)) Adjust the ranges to suit your data, but you can't use full-column references (unless you are using XL2007, in which case you could probably use COUNTIFS). Hope this helps. Pete On Dec 31, 12:45 am, TrainingGuru wrote: I am using the following formula to count the number of times "Web" appears in column B based on "1" being in column D: =IF('List of Reviews'!B:B="Web",COUNTIF('List of Reviews'!D:D,1),0) I continue to get a #NUM! error for the logical test in the IF statement even when the formula is reversed and I try to count the number of times "Web" appears for every row which contains a "1" in column D. What is wrong with the formula? Is there another function that will return the correct result? Just a note: The COUNTIF function works each time to find the right value for the criteria entered. Help needed quickly! Thanks. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count the times a value appears in a column based on another v
Hi,
You could enter the formula as an array but first another way to enter it using SUMPRODUCT =SUMPRODUCT(--('List of Reviews'!B1:B1000="Web"),--('List of Reviews'!D1:D1000=1)) The array form of this formula is requires the original notation, which can cause problems for some data sets, and it requires array entry (press Shift+Ctrl+Enter). Note also, that it is shorter, but in general SUMPRODUCT is somewhat faster. =SUMPRODUCT(--('List of Reviews'!B1:B1000="Web"),--('List of Reviews'!D1:D1000=1)) Another note - If you use a range name, for example B and D (bad descriptive names, but I don't know what you data is, so this just are the column letters) then the formula becomes =SUMPRODUCT(--(B="Web"),--(D=1)) The advantage is you can ignore sheet references. -- If this helps, please click the Yes button Cheers, Shane Devenshire "TrainingGuru" wrote: Thank you, Pete. That did the trick. I tried to use SUMPRODUCT and even a form of an array before but wasn't successful. I don't yet understand how this function works. I will need to do more research. Can you suggest a resource? Your response saved the day (actually saved my week!). Thank you again. "Pete_UK" wrote: Try this: =SUMPRODUCT(('List of Reviews'!B1:B1000="Web")*('List of Reviews'! D1:D1000=1)) Adjust the ranges to suit your data, but you can't use full-column references (unless you are using XL2007, in which case you could probably use COUNTIFS). Hope this helps. Pete On Dec 31, 12:45 am, TrainingGuru wrote: I am using the following formula to count the number of times "Web" appears in column B based on "1" being in column D: =IF('List of Reviews'!B:B="Web",COUNTIF('List of Reviews'!D:D,1),0) I continue to get a #NUM! error for the logical test in the IF statement even when the formula is reversed and I try to count the number of times "Web" appears for every row which contains a "1" in column D. What is wrong with the formula? Is there another function that will return the correct result? Just a note: The COUNTIF function works each time to find the right value for the criteria entered. Help needed quickly! Thanks. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count the times a value appears in a column based on another v
Hi Shane,
Thanks. I'm still not clear on the construction of this formula - =SUMPRODUCT(--('List of Reviews'!B1:B1000="Web"),--('List of Reviews'!D1:D1000=1)) Could you explain the use of the "--" and/or direct me to a further explanation of array formulae. Regards, Carol "Shane Devenshire" wrote: Hi, You could enter the formula as an array but first another way to enter it using SUMPRODUCT =SUMPRODUCT(--('List of Reviews'!B1:B1000="Web"),--('List of Reviews'!D1:D1000=1)) The array form of this formula is requires the original notation, which can cause problems for some data sets, and it requires array entry (press Shift+Ctrl+Enter). Note also, that it is shorter, but in general SUMPRODUCT is somewhat faster. =SUMPRODUCT(--('List of Reviews'!B1:B1000="Web"),--('List of Reviews'!D1:D1000=1)) Another note - If you use a range name, for example B and D (bad descriptive names, but I don't know what you data is, so this just are the column letters) then the formula becomes =SUMPRODUCT(--(B="Web"),--(D=1)) The advantage is you can ignore sheet references. -- If this helps, please click the Yes button Cheers, Shane Devenshire "TrainingGuru" wrote: Thank you, Pete. That did the trick. I tried to use SUMPRODUCT and even a form of an array before but wasn't successful. I don't yet understand how this function works. I will need to do more research. Can you suggest a resource? Your response saved the day (actually saved my week!). Thank you again. "Pete_UK" wrote: Try this: =SUMPRODUCT(('List of Reviews'!B1:B1000="Web")*('List of Reviews'! D1:D1000=1)) Adjust the ranges to suit your data, but you can't use full-column references (unless you are using XL2007, in which case you could probably use COUNTIFS). Hope this helps. Pete On Dec 31, 12:45 am, TrainingGuru wrote: I am using the following formula to count the number of times "Web" appears in column B based on "1" being in column D: =IF('List of Reviews'!B:B="Web",COUNTIF('List of Reviews'!D:D,1),0) I continue to get a #NUM! error for the logical test in the IF statement even when the formula is reversed and I try to count the number of times "Web" appears for every row which contains a "1" in column D. What is wrong with the formula? Is there another function that will return the correct result? Just a note: The COUNTIF function works each time to find the right value for the criteria entered. Help needed quickly! Thanks. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count the times a value appears in a column based on another v
See
http://www.mcgimpsey.com/excel/doubleneg.html In article , TrainingGuru wrote: Could you explain the use of the "--" and/or direct me to a further explanation of array formulae. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count how many times the same word appears in column | Excel Discussion (Misc queries) | |||
Count the number of times specific text appears in a column | Excel Worksheet Functions | |||
How to count the number of times a string appears in a column? | Excel Worksheet Functions | |||
How do I count how many times a code appears in a column? | Excel Worksheet Functions | |||
How do I count how many times x appears in a column? | Excel Worksheet Functions |