Adding multiple conditions in an array formula (CSE)

[anicora]

Lately I have been replacing a lot of VBA code or complex spreadsheets since I discovered the miracle of pressing CTRL+SHIFT+ENTER.

In the example below, I am using the SUM formula to calculate the total Premium where Regions are ON or CC.

The problem is that I now have to change the formula to accomodate 15 different Regions, probably more in the future. As a work around I could add a VLOOKUP in column D like : if(isna(VLOOKUP(a2,f:f,0)),false,true) and add this column into the array formula, but I am hopeful that there's a way to solve this without adding auxiliary fields.
Any tips will be very helpul!

Many thanks

 

[lenze]

Hi and welcome to the board!!!
CSE formulas ar good tools, but IMHO, they are not the end all!! More than a few and your calculation time really slows down!!
In your example, how and why are ON and CC linked together in determining the premium?

lenze

 

[Domenic]

Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER...

=SUM(IF(ISNUMBER(MATCH(A2:A100,F2:F16,0)),C2:C100))

Or, alternatively, try the following formula that needs to be confirmed with just ENTER...

=SUMPRODUCT(--ISNUMBER(MATCH(A2:A100,F2:F16,0)),C2:C100)

Adjust the ranges, accordingly.

 

[lenze]

I agree with Dominic!! I was going to post the same SUMPRODUCT formula but he beat me to it. But, going back to my previous question, if the Region ON and CC are a subset of another entity, you might be able to do this with Grouping in a Pivot Table

lenze

 

[anicora]

Thank you very much! I did not mean to be discorteous by no introducing myself in the first place, but I've been reading this forum for quite a long time, never had the need to post before though. In my mind perhaps I did not consider myself as a newbie here....

Essentially due to a new regional hierarchy more regions will be added, many more actually.

So I need to build a flexible formula to handle multiple conditions. For now I only have 2 Regions (ON and CC) in cells F2 and F3, but I may need more e.g. BC in F4, NY in F6, AT in F7 and so on to filter the data.

What I am trying to avoid is having to write (A2:A8=F2)+(A2:A8=F3)+(A2:A8=F4)+(A2:A8=F5)+......+(A2:A8=Fn) in the array formula. Something more dynamic that could read the content in column F as the calculation constrains. Would it be possible to do this without writing code?

Thanks

 

[anicora]

it worked like a charm!!!!!!!!!!!!!!

thanks thanks thanks



you guys are awesome!