SUMIFS, with specific value or ALL entries

[alketrazz]

Hi

I have this formula

=SUM(SUMIFS(Data!$F:$F,Data!$P:$P,VolumePlan!$G45,Data!$O:$O,IF(nStockType="ALL",{"GBC","GBN","STK","VOR"},nStockType),Data!$N:$N,IF(nUnit="ALL",{2,4},nUnit)))

This should sum the values in column F

Column P where the value = to column G45

Column O where the value is either GBC, GBN, STK, VOR, or if all selected, include any of the values in the sum

Column N where the value is either 2 or 4, or if all selected, include any of the values in the sum

This is returning nothing. if I exclude the column O part or the column N part it works, but having both, doesn't work.


The other way I thought of doing this was with SUMPRODUCT, but that then relies on knowing how many rows there are as can't reference the entire column.

Any ideas ?

 

[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’)

With two arrays one has to be vertical & the other horizontal.
Try

=SUM(SUMIFS(Data!$F:$F,Data!$P:$P,VolumePlan!$G45,Data!$O:$O,IF(nStockType="ALL",{"GBC","GBN","STK","VOR"},nStockType),Data!$N:$N,IF(nUnit="ALL",{2;4},nUnit)))

 

[alketrazz]

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’)

With two arrays one has to be vertical & the other horizontal.
Try

=SUM(SUMIFS(Data!$F:$F,Data!$P:$P,VolumePlan!$G45,Data!$O:$O,IF(nStockType="ALL",{"GBC","GBN","STK","VOR"},nStockType),Data!$N:$N,IF(nUnit="ALL",{2;4},nUnit)))

Hi... Thanks, I will update my profile.

Thanks, that's perfect and working correctly now, so the only change was the ; making it a vertial array.

 

[Fluff]

You're welcome & thanks for the feedback.

so the only change was the ; making it a vertial array

That's right.