SUMIFS with Multiple Criteria in one column

[gavs73]

Hi, would appreciate any help with this, I want to have multiple option matches against one column, is this possible ?

=SUMIFS('Finance'!$M:$M,'Finance'!$V:$V, OR(AL7,AL8,AL9,AL10,AL11,AL12,AL13),'Finance'!$C:$C, D29)

Thanks
Gavin

 

[Eric W]

Try:

=SUM(SUMIFS('Finance'!$M:$M,'Finance'!$V:$V,AL7:AL13,'Finance'!$C:$C, D29))

with Control+Shift+Enter (depending on what version of Excel you have).

 

[gaz_chops]

Try

=SUM(SUMIFS('Finance'!$M:$M,'Finance'!$V:$V,{AL7,AL8,AL9,AL10,AL11,AL12,AL13},'Finance'!$C:$C, D29))

 

[Eric W]

Try

=SUM(SUMIFS('Finance'!$M:$M,'Finance'!$V:$V,{AL7,AL8,AL9,AL10,AL11,AL12,AL13},'Finance'!$C:$C, D29))

If AL7, AL8, etc. were values, not cell addresses, you could put them in an array constant like this, but you'd still need to put quotes around each one. If they are cell addresses, then you need to do it as I showed in post 2. The Control+Shift+Enter will be required unless you have Excel 365 with the new calculation engine. But the formula in post 3 will not work as shown.

 

[gaz_chops]

Thanks @Eric W that is what I did in my test data! Don't know why I posted it like that.......... must be coffee time...........

 

[gavs73]

thank you all appreciate the help

 

[Eric W]

Glad we could help! ?