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:D119 96="x")*(Cleaned!H2:H11996="s"))

Mike

"lakwriter" wrote:

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

=SUMPRODUCT(--(Cleaned!D2:D11996="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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"lakwriter" wrote in message
...
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

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))

--
Biff
Microsoft Excel MVP


"lakwriter" wrote in message
...
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

=SUMPRODUCT((D2:D1196="X")*(A2:A1196="1")*(E2:E119 6="S"))

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

--
Greetings from New Zealand

"lakwriter" wrote in message
...
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

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

change the range to your needs

"lakwriter" wrote:

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

Ah! but only if you have XL2007
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Eduardo" wrote in message
...
Try
=COUNTIFS(H2:H9,"S",D2:D9,"x",A2:A9,"1"),

change the range to your needs

"lakwriter" wrote:

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

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

"Eduardo" wrote:

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

change the range to your needs

"lakwriter" wrote:

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,

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

"lakwriter" wrote in message
...
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,

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

"lakwriter" wrote:

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