Conditional IF's

[lbgolferga]

I am trying to count the number of times a conditional value occurs in a spreadsheet. I would like to check column A:1-end for a value "AAA". If true then check column D, same row for one of 3 values. If any are true add 1 to the total. I have tried this:
=sum(if(a1:A100="aaa",if(d1:d100="LB",1,0),0)) as an array function and it works but only for one conditional occurance. Can I check for all three with one statment? I.E. If col-a="aaa" and (col-d = ("LB" or "PR" or "MG") , add 1 to total.

 

[Aladin Akyurek]

On 2002-03-06 10:27, lbgolferga wrote:
I am trying to count the number of times a conditional value occurs in a spreadsheet. I would like to check column A:1-end for a value "AAA". If true then check column D, same row for one of 3 values. If any are true add 1 to the total. I have tried this:
=sum(if(a1:A100="aaa",if(d1:d100="LB",1,0),0)) as an array function and it works but only for one conditional occurance. Can I check for all three with one statment? I.E. If col-a="aaa" and (col-d = ("LB" or "PR" or "MG") , add 1 to total.

Try:

=SUMPRODUCT((A2:A4="aaa")*(D2:D4={"LB","PR","MG"}))

 

[lbgolferga]

On 2002-03-06 12:01, Aladin Akyurek wrote:

On 2002-03-06 10:27, lbgolferga wrote:
I am trying to count the number of times a conditional value occurs in a spreadsheet. I would like to check column A:1-end for a value "AAA". If true then check column D, same row for one of 3 values. If any are true add 1 to the total. I have tried this:
=sum(if(a1:A100="aaa",if(d1:d100="LB",1,0),0)) as an array function and it works but only for one conditional occurance. Can I check for all three with one statment? I.E. If col-a="aaa" and (col-d = ("LB" or "PR" or "MG") , add 1 to total.

Try:

=SUMPRODUCT((A2:A4="aaa")*(D2:D4={"LB","PR","MG"}))

This solution worked beautifully, Thank You. Now one additional twist. In checking Column A: could you also check for multiple values? I.E. check for "AAA" or "BBB" then if true check check Column D: for ("LB","PR","MG"). I tried plugging it in the formula as an array on Column a but it did not seem to work.

 

[Aladin Akyurek]

On 2002-03-06 15:22, lbgolferga wrote:

On 2002-03-06 12:01, Aladin Akyurek wrote:

On 2002-03-06 10:27, lbgolferga wrote:
I am trying to count the number of times a conditional value occurs in a spreadsheet. I would like to check column A:1-end for a value "AAA". If true then check column D, same row for one of 3 values. If any are true add 1 to the total. I have tried this:
=sum(if(a1:A100="aaa",if(d1:d100="LB",1,0),0)) as an array function and it works but only for one conditional occurance. Can I check for all three with one statment? I.E. If col-a="aaa" and (col-d = ("LB" or "PR" or "MG") , add 1 to total.

Try:

=SUMPRODUCT((A2:A4="aaa")*(D2:D4={"LB","PR","MG"}))

This solution worked beautifully, Thank You. Now one additional twist. In checking Column A: could you also check for multiple values? I.E. check for "AAA" or "BBB" then if true check check Column D: for ("LB","PR","MG"). I tried plugging it in the formula as an array on Column a but it did not seem to work.

That's right: You need to "fix" one set of values against to the other set of values with which you want to do OR'ing.

=SUMPRODUCT(((A2:A4="aaa")*(D2:D4={"LB","PR","MG"})+(A2:A4="X")*(D2:D4={"LB","PR","MG"})))

Aladin

 

[lbgolferga]

On 2002-03-06 21:51, Aladin Akyurek wrote:

On 2002-03-06 15:22, lbgolferga wrote:

On 2002-03-06 12:01, Aladin Akyurek wrote:

On 2002-03-06 10:27, lbgolferga wrote:
I am trying to count the number of times a conditional value occurs in a spreadsheet. I would like to check column A:1-end for a value "AAA". If true then check column D, same row for one of 3 values. If any are true add 1 to the total. I have tried this:
=sum(if(a1:A100="aaa",if(d1:d100="LB",1,0),0)) as an array function and it works but only for one conditional occurance. Can I check for all three with one statment? I.E. If col-a="aaa" and (col-d = ("LB" or "PR" or "MG") , add 1 to total.

Try:

=SUMPRODUCT((A2:A4="aaa")*(D2:D4={"LB","PR","MG"}))

This solution worked beautifully, Thank You. Now one additional twist. In checking Column A: could you also check for multiple values? I.E. check for "AAA" or "BBB" then if true check check Column D: for ("LB","PR","MG"). I tried plugging it in the formula as an array on Column a but it did not seem to work.

That's right: You need to "fix" one set of values against to the other set of values with which you want to do OR'ing.

=SUMPRODUCT(((A2:A4="aaa")*(D2:D4={"LB","PR","MG"})+(A2:A4="X")*(D2:D4={"LB","PR","MG"})))

Aladin

Thank You again, it works great.

LB