array formula troubles

[Carolyn]

Alas! I must be missing something in my formula. I am
entering a very simple array formula which is as follows:

{=if(O2:O44=100,sum(P244))}
{=if(O2:O44=100,sum(P244))}

In column O there are two choices, either 100 or 140.
What I need is a total from column P for each 100 and 140
that exists in O. I can get a total for 140 but I can not
get a total for 100. Instead I get a FALSE response. It
is as though the formula is only applying the IF criteria
to the first row of my range. The first row reads 140 in
O2. If I change it to 100 then the FALSE is now replaced
with a number.
Question: How can I get my array formula using the IF
statement criteria to apply to the entire range of rows
rather then just the first row?
Many thanks!

 

[Peo Sjoblom]

I am not entirely sure I understand you but here goes

=IF(OR(O2:O44=100,O2:O44=140),SUM(P244),"")

array entered with ctrl + shift & enter

 

[Ron Rosenfeld]

Alas! I must be missing something in my formula. I am
entering a very simple array formula which is as follows:

{=if(O2:O44=100,sum(P244))}
{=if(O2:O44=100,sum(P244))}

In column O there are two choices, either 100 or 140.
What I need is a total from column P for each 100 and 140
that exists in O. I can get a total for 140 but I can not
get a total for 100. Instead I get a FALSE response. It
is as though the formula is only applying the IF criteria
to the first row of my range. The first row reads 140 in
O2. If I change it to 100 then the FALSE is now replaced
with a number.
Question: How can I get my array formula using the IF
statement criteria to apply to the entire range of rows
rather then just the first row?
Many thanks!

Your SUM is in the wrong place:

Array enter:

=SUM(IF(O2:O44=100,P244,""))

Another formula that will give the same result:

=SUMIF(O2:O44,100,P244)




--ron

 

[Carolyn]

Aha! Thank you very much Ron!

-----Original Message-----


Your SUM is in the wrong place:

Array enter:

=SUM(IF(O2:O44=100,P244,""))

Another formula that will give the same result:

=SUMIF(O2:O44,100,P244)




--ron
.