Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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, |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTIF COMBINATION?? | Excel Worksheet Functions | |||
Combining IF and COUNTIF based on two columns | Excel Discussion (Misc queries) | |||
COUNTIF in one colum then COUNTIF in another...??? | Excel Worksheet Functions | |||
Countif - Countif | Excel Worksheet Functions | |||
countif, again | Excel Worksheet Functions |