array formula troubles

  • Thread starter Thread starter Carolyn
  • Start date Start date
C

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(P2:P44))}
{=if(O2:O44=100,sum(P2:P44))}

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!
 
I am not entirely sure I understand you but here goes

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

array entered with ctrl + shift & enter
 
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(P2:P44))}
{=if(O2:O44=100,sum(P2:P44))}

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!
Click to expand...

Your SUM is in the wrong place:

Array enter:

=SUM(IF(O2:O44=100,P2:P44,""))

Another formula that will give the same result:

=SUMIF(O2:O44,100,P2:P44)




--ron
 
Aha! Thank you very much Ron!

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


Your SUM is in the wrong place:

Array enter:

=SUM(IF(O2:O44=100,P2:P44,""))

Another formula that will give the same result:

=SUMIF(O2:O44,100,P2:P44)




--ron
.
Click to expand...
 
Back
Top