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

 

[ChrisRamsden]

Hi Karan,

What do you want your final criteria to be? Your currently referencing blanks and a specific range, but they aren't linked up?

Thanks

 

[KrushSheth]

Hi Chris,

thanks for replying, the final criteria is looking up a column on the source data (H:H) and returning values which do not equal to the data held in cells "D5:D59". There is data in the cells I am referencing to.

I've remember that the array within criteria 2 will require the formula to be adjusted, therefore, I've changed the formula above to the below to cater for the 2nd criteria:

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

Is this the question you were asking?

Thanks.

 

[majidsiddique]

try this way

[TABLE="width: 512"]
<colgroup><col width="64" span="8" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl66, width: 64"]CAT-1[/TD]
[TD="class: xl66, width: 64"]CAT-2[/TD]
[TD="class: xl66, width: 64"]QTY[/TD]
[TD="class: xl66, width: 64"] [/TD]
[TD="class: xl66, width: 64"]CAT-1[/TD]
[TD="class: xl66, width: 64"]CAT-2[/TD]
[TD="class: xl66, width: 64"]Total[/TD]
[TD="class: xl66, width: 64"] [/TD]
[/TR]
[TR]
[TD="class: xl65"]Jeans[/TD]
[TD="class: xl65"]Blue[/TD]
[TD="class: xl65"]10[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"]Jeans[/TD]
[TD="class: xl65"]Red[/TD]
[TD="class: xl65"]100[/TD]
[TD="class: xl65"]100[/TD]
[/TR]
[TR]
[TD="class: xl65"]Jeans[/TD]
[TD="class: xl65"]Red[/TD]
[TD="class: xl65"]20[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"]Jeans[/TD]
[TD="class: xl65"]Blue[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"]50[/TD]
[/TR]
[TR]
[TD="class: xl65"]Jeans[/TD]
[TD="class: xl65"]Blue[/TD]
[TD="class: xl65"]10[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD="class: xl65"]Jeans[/TD]
[TD="class: xl65"]Blue[/TD]
[TD="class: xl65"]10[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD="class: xl65"]Jeans[/TD]
[TD="class: xl65"]Red[/TD]
[TD="class: xl65"]20[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD="class: xl65"]Jeans[/TD]
[TD="class: xl65"]Blue[/TD]
[TD="class: xl65"]10[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD="class: xl65"]Jeans[/TD]
[TD="class: xl65"]Blue[/TD]
[TD="class: xl65"]10[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD="class: xl65"]Jeans[/TD]
[TD="class: xl65"]Red[/TD]
[TD="class: xl65"]20[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD="class: xl65"]Jeans[/TD]
[TD="class: xl65"]Red[/TD]
[TD="class: xl65"]20[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD="class: xl65"]Jeans[/TD]
[TD="class: xl65"]Red[/TD]
[TD="class: xl65"]20[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[/TR]
</tbody>[/TABLE]

use formula total cell:

=sumifs(C1:C12,A1:A12,"Jeans",B2:B12,"Red")
=sumifs(C1:C12,A1:A12,"Jeans",B2:B12,"Blue")

you can use cell ref as well replace of "Red" and "Blue", like F2,E2

 

[KrushSheth]

Thanks Majid, however, it would be impractical for my data set as there are 55 different dimensions within a category.

The formula is meant to return the total of a column that does not have the same dimensions above. Therefore, this returned number will be the balancing number against the original data's total. I could in theory just sum the above totals to be deducted from the formula without my final criteria, however, there are multiple datasets being calculated in there. The formula I've asked for help is just one of the formulas in this cell. The others do return a value.

regards,
Karan ShethThanks Majid, however, it would be impractical for my data set as there are 55 different dimensions within a category.

The formula is meant to return the total of a column that does not have the same dimensions above. Therefore, this returned number will be the balancing number against the original data's total. I could in theory just sum the above totals to be deducted from the formula without my final criteria, however, there are multiple datasets being calculated in there. The formula I've asked for help is just one of the formulas in this cell. The others do return a value.

regards,
Karan Sheth

 

[ChrisRamsden]

Hi Karan,

I'm not going to have time to look at this today but i will however be able to tomorrow. If you do not have a solution by then, i will see if i can work it out for you. To give you something different to look at you could replace your final criteria with 55 different criteria, referencing each cell in the range D5:D59. I know this is long winded and not ideal but it should work. Depends on how long the rest of the forumla is that you haven't included.

=Sum(SUMIFS($T:$T, $U:$U,$BJ$65, $G:$G, {"Post-Paid","Business"}, $H:$H, "<>"D5, $H:$H, "<>"D6, $H:$H, "<>"D7, $H:$H, "<>"D8, etc))

Hope this helps for now.

Thanks

 

[KrushSheth]

Thanks Chris, I have currently done as you've mentioned - however, I just wanted to see if there is a more efficient way of performing this calculation on excel.

I would 100% appreciate any feedback that can create a more efficient formula and can wait.

Regards,
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.

 

[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.

 

[planaesthete]

Late contribution, but I do not see any previous answer for how to do this in one formula for an arbitrarily sized array. I cannot say this is perfectly elegant, but it works…

The essential problem is that SUMIFS() criteria do not accommodate boolean connectives (beyond the superior AND).

Only XOR can be simulated arithmetically by effectively repeating SUMIFS() for each array value, then summing the results.

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

This only works if the criteria per row cannot be satisfied in more than one instance of SUMIFS() (i.e. for one array value). If satisfied more than once, the value will be duplicated in the summation. Hence, this works for the case of summing when records do match any value in an array, but only if the array does not contain duplicates. ⚠

To sum when records do not match any value in an array, it would be necessary to simulate NOR. For an array of size N, repeating SUMIFS() for each array value will count every value you want N times and each you do not want N-1 times (assuming no duplicates). There is no arithmetic operation to directly unpick that, so an equivalent of the above SUM() is impossible. The only way to simulate for NOR is to have a little repetition in the formula.

Once you can accept that, the solution is simple‡:

Subtract the sum for lines that do match an array value from the overall sum (matching other criteria, but ignoring this one).

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

(If there are no other criteria, start with SUM() instead.)
Note, again, the array must not contain duplicates. ⚠ †

‡
(In the product of more than two criteria, AND* is satisfied when more than one criterion is, even if not ALL).
NOR == ( NOT(AND*) ) AND ( NOT(XOR) ) → Σ x[NOR] = Σ x[TRUE] - Σ x[AND*] - Σ x[XOR] + Σ x[AND*&XOR]
Necessarily, Σ x[AND*&XOR] = 0 . It would help to assume Σ x[AND*] = 0 . † …