| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUM PRODUCT
Hi,
Does anyone know how I can add =COUNTIF(B19:B21=A9) in to the following formula:- =SUMPRODUCT(1/COUNTIF(E19:E23,E19:E23&"")) I want it to do both things at the same time but cannot work out where/how to put it??? Thanks Fiona |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUM PRODUCT
Can you tell us in plain words what do you want to do and provide a sample of
your data structure? Regards, Stefi €˛Fiona Yorke-Saville€¯ ezt Ć*rta: Hi, Does anyone know how I can add =COUNTIF(B19:B21=A9) in to the following formula:- =SUMPRODUCT(1/COUNTIF(E19:E23,E19:E23&"")) I want it to do both things at the same time but cannot work out where/how to put it??? Thanks Fiona |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUM PRODUCT
Hi Stefi,
thanks for your e-mail, I've listed below what I want to do, does this make sense? A B 1 ABC 2 ABC AB/12345 IF A2:A6 = A1 then count B2:B6 (not counting any duplicates) 3 BCD AB/12345 The answer I need is 3 4 CDE AB/23584 5 ABC AB/32586 6 ABC AB/36987 "Stefi" wrote: Can you tell us in plain words what do you want to do and provide a sample of your data structure? Regards, Stefi €˛Fiona Yorke-Saville€¯ ezt Ć*rta: Hi, Does anyone know how I can add =COUNTIF(B19:B21=A9) in to the following formula:- =SUMPRODUCT(1/COUNTIF(E19:E23,E19:E23&"")) I want it to do both things at the same time but cannot work out where/how to put it??? Thanks Fiona |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUM PRODUCT
Try this:
=CEILING(SUMPRODUCT(--($A$1=$A$2:$A$6),1/COUNTIF(B2:B6,B2:B6&"")),1) Regards, Stefi €˛Fiona Yorke-Saville€¯ ezt Ć*rta: Hi Stefi, thanks for your e-mail, I've listed below what I want to do, does this make sense? A B 1 ABC 2 ABC AB/12345 IF A2:A6 = A1 then count B2:B6 (not counting any duplicates) 3 BCD AB/12345 The answer I need is 3 4 CDE AB/23584 5 ABC AB/32586 6 ABC AB/36987 "Stefi" wrote: Can you tell us in plain words what do you want to do and provide a sample of your data structure? Regards, Stefi €˛Fiona Yorke-Saville€¯ ezt Ć*rta: Hi, Does anyone know how I can add =COUNTIF(B19:B21=A9) in to the following formula:- =SUMPRODUCT(1/COUNTIF(E19:E23,E19:E23&"")) I want it to do both things at the same time but cannot work out where/how to put it??? Thanks Fiona |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUM PRODUCT
Thanks Stefi! That's exactly it :) :)
"Stefi" wrote: Try this: =CEILING(SUMPRODUCT(--($A$1=$A$2:$A$6),1/COUNTIF(B2:B6,B2:B6&"")),1) Regards, Stefi €˛Fiona Yorke-Saville€¯ ezt Ć*rta: Hi Stefi, thanks for your e-mail, I've listed below what I want to do, does this make sense? A B 1 ABC 2 ABC AB/12345 IF A2:A6 = A1 then count B2:B6 (not counting any duplicates) 3 BCD AB/12345 The answer I need is 3 4 CDE AB/23584 5 ABC AB/32586 6 ABC AB/36987 "Stefi" wrote: Can you tell us in plain words what do you want to do and provide a sample of your data structure? Regards, Stefi €˛Fiona Yorke-Saville€¯ ezt Ć*rta: Hi, Does anyone know how I can add =COUNTIF(B19:B21=A9) in to the following formula:- =SUMPRODUCT(1/COUNTIF(E19:E23,E19:E23&"")) I want it to do both things at the same time but cannot work out where/how to put it??? Thanks Fiona |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUM PRODUCT
You are welcome! Thanks for the feedback!
Stefi €˛Fiona Yorke-Saville€¯ ezt Ć*rta: Thanks Stefi! That's exactly it :) :) "Stefi" wrote: Try this: =CEILING(SUMPRODUCT(--($A$1=$A$2:$A$6),1/COUNTIF(B2:B6,B2:B6&"")),1) Regards, Stefi €˛Fiona Yorke-Saville€¯ ezt Ć*rta: Hi Stefi, thanks for your e-mail, I've listed below what I want to do, does this make sense? A B 1 ABC 2 ABC AB/12345 IF A2:A6 = A1 then count B2:B6 (not counting any duplicates) 3 BCD AB/12345 The answer I need is 3 4 CDE AB/23584 5 ABC AB/32586 6 ABC AB/36987 "Stefi" wrote: Can you tell us in plain words what do you want to do and provide a sample of your data structure? Regards, Stefi €˛Fiona Yorke-Saville€¯ ezt Ć*rta: Hi, Does anyone know how I can add =COUNTIF(B19:B21=A9) in to the following formula:- =SUMPRODUCT(1/COUNTIF(E19:E23,E19:E23&"")) I want it to do both things at the same time but cannot work out where/how to put it??? Thanks Fiona |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| What is sum product... | Excel Discussion (Misc queries) | |||
| Sum-if-product | Excel Worksheet Functions | |||
| product key | Setting up and Configuration of Excel | |||
| Sum if or sum product? | Excel Worksheet Functions | |||
| Sum Product help | Excel Worksheet Functions | |||
Linear Mode
