additional conditions to SUMPRODUCT formula

  • Thread starter Fiona Yorke-Saville
  • Start date
F

Fiona Yorke-Saville

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
 
J

JCS

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
 
F

Fiona Yorke-Saville

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...?
 
J

JCS

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"",SUMPRODUCT(1/COUNTIF(A1:A10,A1:A10&"""")))
)"

John
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top