Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,346
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4,624
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Count how many times the same word appears in column Alexa Excel Discussion (Misc queries) 6 April 9th 23 12:51 PM
Count the number of times specific text appears in a column Nannie Excel Worksheet Functions 5 August 27th 08 07:09 PM
How to count the number of times a string appears in a column? btsray Excel Worksheet Functions 0 January 25th 06 11:15 PM
How do I count how many times a code appears in a column? Leo Excel Worksheet Functions 3 June 2nd 05 08:50 PM
How do I count how many times x appears in a column? Ian Roberts Excel Worksheet Functions 2 November 7th 04 04:13 PM


All times are GMT +1. The time now is 11:32 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"