countif
 

Hi there!
I have a spreadsheet wiht 5 colums; I want to count how many times a product
appears on column E, only IF cells on column A= 1000.

Anyone?
regards,

One way:

=SUMPRODUCT(--(A1:A2000=1000), --(E1:E2000="<product"))

http://www.mcgimpsey.com/excel/doubleneg.html


In article ,
"Roger" wrote:

I have a spreadsheet wiht 5 colums; I want to count how many times a product
appears on column E, only IF cells on column A= 1000.

Hi there,

Many thanks for your help, but the formula is returning wiht 0(zero), value
- Is there a specific place I should write the formula?
regards,
Roger

"JE McGimpsey" wrote:

One way:

=SUMPRODUCT(--(A1:A2000=1000), --(E1:E2000="<product"))

http://www.mcgimpsey.com/excel/doubleneg.html


In article ,
"Roger" wrote:

I have a spreadsheet wiht 5 colums; I want to count how many times a product
appears on column E, only IF cells on column A= 1000.

Don't put the formula in A1:A2000 and not in E1:E2000.

But do make sure you change "<product" to the real name.

If this doesn't help, post back the formula you used.

Roger wrote:

Hi there,

Many thanks for your help, but the formula is returning wiht 0(zero), value
- Is there a specific place I should write the formula?
regards,
Roger

"JE McGimpsey" wrote:

One way:

=SUMPRODUCT(--(A1:A2000=1000), --(E1:E2000="<product"))

http://www.mcgimpsey.com/excel/doubleneg.html


In article ,
"Roger" wrote:

I have a spreadsheet wiht 5 colums; I want to count how many times a product
appears on column E, only IF cells on column A= 1000.


--

Dave Peterson

Once again many thanks for your help.

The formula I have used is :
=SUMPRODUCT(--(A3:a2002=109266),--(e3:e2002="AS400"))
The result in the cell is zero, however when I press the fx on the formula
bar, the correct number appears.

Best regards,
Roger

"Dave Peterson" wrote:

Don't put the formula in A1:A2000 and not in E1:E2000.

But do make sure you change "<product" to the real name.

If this doesn't help, post back the formula you used.

Roger wrote:

Hi there,

Many thanks for your help, but the formula is returning wiht 0(zero), value
- Is there a specific place I should write the formula?
regards,
Roger

"JE McGimpsey" wrote:

One way:

=SUMPRODUCT(--(A1:A2000=1000), --(E1:E2000="<product"))

http://www.mcgimpsey.com/excel/doubleneg.html


In article ,
"Roger" wrote:

I have a spreadsheet wiht 5 colums; I want to count how many times a product
appears on column E, only IF cells on column A= 1000.


--

Dave Peterson

Do you have calculation set to automatic?

Tools|Options|Calculation tab


Roger wrote:

Once again many thanks for your help.

The formula I have used is :
=SUMPRODUCT(--(A3:a2002=109266),--(e3:e2002="AS400"))
The result in the cell is zero, however when I press the fx on the formula
bar, the correct number appears.

Best regards,
Roger

"Dave Peterson" wrote:

Don't put the formula in A1:A2000 and not in E1:E2000.

But do make sure you change "<product" to the real name.

If this doesn't help, post back the formula you used.

Roger wrote:

Hi there,

Many thanks for your help, but the formula is returning wiht 0(zero), value
- Is there a specific place I should write the formula?
regards,
Roger

"JE McGimpsey" wrote:

One way:

=SUMPRODUCT(--(A1:A2000=1000), --(E1:E2000="<product"))

http://www.mcgimpsey.com/excel/doubleneg.html


In article ,
"Roger" wrote:

I have a spreadsheet wiht 5 colums; I want to count how many times a product
appears on column E, only IF cells on column A= 1000.


--

Dave Peterson


--

Dave Peterson

Dave,

Its ok now...not sure what happened! Anyway...thank you ever so much for
your help and time!
Best Regards,
Roger

"Dave Peterson" wrote:

Do you have calculation set to automatic?

Tools|Options|Calculation tab


Roger wrote:

Once again many thanks for your help.

The formula I have used is :
=SUMPRODUCT(--(A3:a2002=109266),--(e3:e2002="AS400"))
The result in the cell is zero, however when I press the fx on the formula
bar, the correct number appears.

Best regards,
Roger

"Dave Peterson" wrote:

Don't put the formula in A1:A2000 and not in E1:E2000.

But do make sure you change "<product" to the real name.

If this doesn't help, post back the formula you used.

Roger wrote:

Hi there,

Many thanks for your help, but the formula is returning wiht 0(zero), value
- Is there a specific place I should write the formula?
regards,
Roger

"JE McGimpsey" wrote:

One way:

=SUMPRODUCT(--(A1:A2000=1000), --(E1:E2000="<product"))

http://www.mcgimpsey.com/excel/doubleneg.html


In article ,
"Roger" wrote:

I have a spreadsheet wiht 5 colums; I want to count how many times a product
appears on column E, only IF cells on column A= 1000.


--

Dave Peterson


--

Dave Peterson