Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
additional conditions to SUMPRODUCT formula
Hi
I wondered if anyone knew if I can add another condition to this formula. =SUMPRODUCT(1/COUNTIF(A1:A10,A1:A10&"")) I want to use the above formula if coloumn b contains c1 e.g Coloumn A Coloumn B 1111 X 1111 X 1234 Y 1258 Z 1254 Y I want to count Coloumn A (as detailed in the above formula) if Coloumn B contains X (or cell C1) Is that too many formulas? Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
additional conditions to SUMPRODUCT formula
Hi Fiona,
I'm not sure why you would want to count column A when whatt you are counting is in column B. If you are counting the x's in column B and whatever the contents are in C1, the following formula should work: =SUMPRODUCT((B1:B10="x")+(B1:B10=C1)) HTH, John "Fiona Yorke-Saville" wrote: Hi I wondered if anyone knew if I can add another condition to this formula. =SUMPRODUCT(1/COUNTIF(A1:A10,A1:A10&"")) I want to use the above formula if coloumn b contains c1 e.g Coloumn A Coloumn B 1111 X 1111 X 1234 Y 1258 Z 1254 Y I want to count Coloumn A (as detailed in the above formula) if Coloumn B contains X (or cell C1) Is that too many formulas? Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
additional conditions to SUMPRODUCT formula
Thanks John but I wanted to count column A only if Coloumn B contains a
certiain letter. I wasn't sure if this was possible? The sumproduct formula makes sure dupplicate numbers aren't counted but I couldn't figure out a way to add a COUNTIF(B1:B10,x) in to the sumproduct one...? "JCS" wrote: Hi Fiona, I'm not sure why you would want to count column A when whatt you are counting is in column B. If you are counting the x's in column B and whatever the contents are in C1, the following formula should work: =SUMPRODUCT((B1:B10="x")+(B1:B10=C1)) HTH, John "Fiona Yorke-Saville" wrote: Hi I wondered if anyone knew if I can add another condition to this formula. =SUMPRODUCT(1/COUNTIF(A1:A10,A1:A10&"")) I want to use the above formula if coloumn b contains c1 e.g Coloumn A Coloumn B 1111 X 1111 X 1234 Y 1258 Z 1254 Y I want to count Coloumn A (as detailed in the above formula) if Coloumn B contains X (or cell C1) Is that too many formulas? Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
additional conditions to SUMPRODUCT formula
Hi Fiona,
Don't know if you are still monitoring this posting, but try this formula: "=IF(COUNTIF(B1:B5,""x"")0,SUMPRODUCT(1/COUNTIF(A1:A10,A1:A10&"""")),IF(C1=""x"",SUMPRODUC T(1/COUNTIF(A1:A10,A1:A10&""""))) )" John "Fiona Yorke-Saville" wrote: Hi I wondered if anyone knew if I can add another condition to this formula. =SUMPRODUCT(1/COUNTIF(A1:A10,A1:A10&"")) I want to use the above formula if coloumn b contains c1 e.g Coloumn A Coloumn B 1111 X 1111 X 1234 Y 1258 Z 1254 Y I want to count Coloumn A (as detailed in the above formula) if Coloumn B contains X (or cell C1) Is that too many formulas? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Please help with Sumproduct, adding additional criteria | Excel Worksheet Functions | |||
Sumproduct with conditions | Excel Discussion (Misc queries) | |||
SUMPRODUCT - Additional criteria | Excel Worksheet Functions | |||
Additional Sumproduct Criterias | Excel Worksheet Functions | |||
Additional Conditions for Conditional Formatting | Excel Worksheet Functions |