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.

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.

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.

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.

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.

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.

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.