Sumifs - does not equal to an array

[KrushSheth]

Hi,

I hope an you experts can help. If I have phrased the question poorly, please ask for more information. This is my first post and I'm a newb at excel.

I need to create a sumifs formula (unless you can advise an alternative) which will allow me to sum a column which does not have the same certain dimensions that are current in the report.

I have used the following formula, however, no value is being returned.

=SUMIFS($T:$T, $U:$U,$BJ$65, $G:$G, {"Post-Paid","Business"}, $H:$H, "<>"&D5:D59)

The area of the formula were I require assistance is the final criteria. The 2nd criteria works - having broken the formula down to check.

Is this possible? is there a better way?

Thanks in advance!

Karan

 

[KrushSheth]

I've found an more easier workaround to the above, created another column in the dataset to index the dimensions in question and return a single result if already present in my original dataset. The sumif above instead now looks at this column and returns only values which do not have the above result.

It still would be nice to learn how I can actually carry out a SUMIFS with a does not equal to a array of cells rather than 1 cell.

BTW this worked around solution is impractical to maintain longer term as the monthly datasets that are analysed for the report have more than 450,000 lines; therefore, adding mapping columns to the dataset make it very impractical to work with due to the speed.

 

[analyst44]

Noob didn't know the XLS wrapping option would do that, sorry. Those formulae, more legibly:

= SUM ( SUMIFS ( values , {other ranges, criteria} , range , array ) )

= SUMIFS ( values , {other ranges, criteria} ) - SUM ( SUMIFS ( values , {other ranges, criteria} , range , array )

So interesting that you posted this so recently @planaesthete. Just needed it today! Thanks for the effort.