Help needed adding third If

[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!D211996="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

 

[Mike H]

Hi,

Maybe with sumproduct instead

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

Mike

 

[Bernard Liengme]

=SUMPRODUCT(--(Cleaned!D211996="x"), --(Cleaned!H2:H11996="S"))
For more details on SUMPRODUCT
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
http://mcgimpsey.com/excel/formulae/doubleneg.html
best wishes

 

[T. Valko]

Try this (normally entered):

=SUMPRODUCT(--(Cleaned!A2:A11996=1),--(Cleaned!D211996="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!D211996=K1),--(Cleaned!H2:H11996=L1))

 

[Bill Kuunders]

=SUMPRODUCT((D21196="X")*(A2:A1196="1")*(E2:E1196="S"))

have a look at www.xldynamic.com/source/xld.sumproduct.html
for a comprehensive explanation

 

[Eduardo]

Try
=COUNTIFS(H2:H9,"S",D29,"x",A2:A9,"1"),

change the range to your needs

 

[Bernard Liengme]

Ah! but only if you have XL2007

 

[lakwriter]

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

 

[Ashish Mathur]

Hi,

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

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

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Shane Devenshire]

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(--(D211996&A2:A11996&E2:E11996="X1S"))

If this helps, please click the Yes button.

Cheers,
Shane Devenshire