Help needed adding third If

  • Thread starter Thread starter lakwriter
  • Start date Start date
L

lakwriter

Hello all, I am having difficulty integrating a third condition into a
complex IF statement.

I have data on one worksheet and my table with formulas on a separate
worksheet.

I want to know how many records (2:1996) have a value of "S" when column
A="1" AND column D="X".

I can check for one value using the formula
=SUM(IF(Cleaned!D2:D11996="x",IF(Cleaned!H2:H11996="S",1,0),0))

But I cannot figure out how to make it check both columns before totaling
the frequency of "s".

Any help is greatly appreciated.
Leslie
 
Hi,

Maybe with sumproduct instead

=SUMPRODUCT((Cleaned!A2:A11996=1)*(Cleaned!D2:D11996="x")*(Cleaned!H2:H11996="s"))

Mike
 
Try this (normally entered):

=SUMPRODUCT(--(Cleaned!A2:A11996=1),--(Cleaned!D2:D11996="x"),--(Cleaned!H2:H11996="s"))

Better to ue cells to hold the criteria:

J1 = 1
K1 = x
L1 = s

=SUMPRODUCT(--(Cleaned!A2:A11996=J1),--(Cleaned!D2:D11996=K1),--(Cleaned!H2:H11996=L1))
 
Thanks so much for all the help, everyone. Nope, don't have XLS 2007 yet,
but the previous formulas appear to work for my needs.

Leslie
 
Hi,

You can also try the following array formula (Ctrl+Shift+Enter)

=sum(if((Cleaned!D2:D11996="x")*(Cleaned!A2:A11996=1)*(Cleaned!D2:D11996="S"),1))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 
Hi,

A few notes: if the entry in column A is the number 1 some of the
suggestions will fail, so you must adjust them by removing the "".
Similarly, if the entry is text in column A test for A=1 will fail, so you
must adjust the formulas by adding "1".

Just to be cute, the following formula deals with both text or number

=SUMPRODUCT(--(D2:D11996&A2:A11996&E2:E11996="X1S"))

If this helps, please click the Yes button.

Cheers,
Shane Devenshire
 

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

Back
Top