Setting 'OR' Criteria on SUMIF

[Barefoot]

Good morning all

I have entered the following array formula which at first glance appears to work. However it is not picking up the first set of criteria (=SD4600 or =SD4650) instead it is summing all based on the remaining criteria?

=SUM(IF(OR(Aptos!$F$7:$F$500="SD4600",Aptos!$F$7:$F$500="SD4650"),IF(Aptos!$E$7:$E$500="Spend to Period",IF(Aptos!$C$7:$C$500='Central MMR'!$A10,Aptos!$J$7:$J$500))))

Initially I had these as two SUMIF formulas added together (Sum(if…. + Sum(if….)) which works fine but is a bit scary looking and thought there might be a way to incorporate my criteria ‘double’ multiple criteria for column F.

Additionally would I need to replace ‘=’ with ‘<>’ to get the reverse? i.e. = anything except SD4600 & SD4650 as I need to sum on similar criteria for all cost centres except SD4600 & SD4650 and I am not sure how to do this otherwise

Cheers
Julian

 

[barry houdini]

Perhaps

=SUMPRODUCT(--(LEFT(Aptos!$F$7:$F$500,4)="SD48"),--(Aptos!$E$7:$E$500="Spend to Period"),--(Aptos!$C$7:$C$500='Central MMR'!$A10),Aptos!$J$7:$J$500)

 

[Barefoot]

Hi Krishnakumar

Thanks, I have quite a bit to obsorb her and will help me with other tasks that I do.

So again a big thank you for all your help.

Best Regards
Julian

 

[mjseim]

I was searching for help on my problem and found this thread. Hopefully it's ok that I post on it.

I am trying to do a similar formula. I have adjusted the formulas posted to fit my needs:

=SUM(IF(ISNUMBER(MATCH('Consolidated 55+ List'!B13:HT13,{"54002","54003"},0)),IF('Consolidated 55+ List'!A20:A32=D67,'Consolidated 55+ List'!B20:HT32)))

However, now what I'd like to do is change
{"54002","54003"}
to a range of editable values... something like:
(B64:B94)

So, the resultant formula would be similar to:
=SUM(IF(ISNUMBER(MATCH('Consolidated 55+ List'!B13:HT13,(B64:B94),0)),IF('Consolidated 55+ List'!A20:A32=D67,'Consolidated 55+ List'!B20:HT32)))

However, as you might expect... this does not work. Is there a way to accomplish this task? Thanks.

 

[Barefoot]

Hi mjseim

drop the brakets around the range.

so (B64:B94)
becomes B64:B94

Cheers
Julian

 

[mjseim]

Don't.... I... Feel... Dumb...

Thank you. It works like a charm!