SUMIFS with multiple criteria from 1 column

[BillieHynds]

Hi,

I am currently trying to work a SUMIFS formula looking at multiple criteria which works fine at the moment. We now have an instance where instead of summing if 1 criteria is met, I would like it to sum for 2 criteria in the same column.

=SUMIFS(Data!D:D,Data!A:A,$AO$3,Data!B:B,$AO$4,Data!C:C,$AO$2,Data!G:G,F26,Data!H:H,I26,Data!I:I,M26,Data!J:J,N26)

This is the current formula and where it says $AO$4, id like this to be $AO$4 AND $AO$5.

I've tried using using SUMPRODUCT and have not been successful.

Any help is much appreciated.

Thanks,

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

=sum(SUMIFS(Data!D:D,Data!A:A,$AO$3,Data!B:B,$AO$4:$AO$5,Data!C:C,$AO$2,Data!G:G,F26,Data!H:H,I26,Data!I:I,M26,Data!J:J,N26))

 

[BillieHynds]

Thanks for response and suggestion.

Office 365 version fyi

 

[Fluff]

Office 365 version fyi

Please don't forget to update your profile.

 

[BillieHynds]

All done

 

[Fluff]

Thanks for that, did the formula work?

 

[BillieHynds]

Sorry was just trying, unfortunately not, brings back a 0.

I did try a similar formula myself

 

[BillieHynds]

In the picture i've attached, Criteria2 seems to not link to the actual cell value

 

[Fluff]

Is col B and exact match to AO4 or AO5?

 

[BillieHynds]

Atached images of column B data, along with the criteria in cells AO4 & AO5