Sumifs limit

peter8848

Board Regular
Joined
Oct 7, 2018
Messages
112
Hi,

It looks like sumifs formula has got a lookup limit of 15 digits and is there any better way to work around it? I tried sum(if under assay formula but hope there is something else quicker we can do?

In addition, if I want to use sumif does not equal to a particular value in a cell i tried this =sumifs(G:G,F:F,"<>"&C256) but it does not looks like it is working as it still picks the value equals to the value in C256?

Lastly, with sumifs formula all these criteria are lookup all once and is it possible to have a formula to look up two variance on the same time for a particular row? e.g. with the formular

sumifs(D:D, A:A,"<>Apple",B:B,"<>Yellow",C:C,"<>Thursday")

A B C D
1 apple yellow friday 5
2 pear green friday 6
3.apple purple friday 8
4.apple purple thursday 8
5.apple purple friday 8

so for now the formula gives sum of 6 but I need 22 as I would like the formula to check A:A for not equal apple and B:B not equal to yellow on the same time not separately.

Your help is much appreciated.

Cheers,

Peter
 
Last edited:

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Your formula will not result to 22 because you don't want it to be equal to apple.
Remove the restriction for column A ("<>Apple") and you will get 22.

It is a bit odd to understand but you may also lookup DSUM (check some examples online).
 
Last edited:
Upvote 0
How about a sumproduct?

=SUMPRODUCT(--(NOT((A1:A5="apple")*(B1:B5="yellow"))),--(C1:C5<>"Thursday"),D1:D5)
 
Upvote 0
1. The 15 digit is a limit. But try to produce an example.

2. The "does not equal to a particular value" would succeed. Try to produce an example where it does not.

3. The sample where you want to obtain 22... Control+shift+enter, not just enter:

=SUM(IF((A1:A5&B1:B5="appleyellow")+(A1:A5&C1:C5="applethursday"),0,D1:D5))
 
Upvote 0
Hi bobsan42,

Thanks but i need a formular to get 30 then as below data but removing column A ("<>Apple") will not work?

A B C D
1 apple yellow friday 5
2 pear green friday 6
3.apple purple friday 8
4.apple purple thursday 8
5.apple purple friday 8
6.pear Yellow friday 8
 
Upvote 0
Hi Steve the fish,

this works! thanks you. Is it possible to add more criteria checking i tried the below

=SUMPRODUCT(--(NOT((A1:A5="apple")*(B1:B5="yellow"))),--(NOT((E1:E5="Yes")*(F1:F5="Apr"))),--(C1:C5<>"Thursday"),--(D1:D5)<>"Bad"),G1:G5)

A B C D E F G
1 apple yellow friday good Yes jan 5
2 pear green friday bad No feb 6
3.apple purple friday good Yes Mar 8
4.apple purple thursday bad No Apr 8
5.apple purple friday good Yes Apr 8
 
Upvote 0
Hi Aladin,

Thanks for your reply. Please find the below:

1. we have product hierarchy code like
300070000200000001

2. Tried again it worked thanks

3. With your suggested formular, it looks like to look at combination of the text and I tried it failed as it summed the total figure, is it possible using cell reference (e.g. A256&B256 Something like this) rather than appleyellow" or "applethursday"? I think this is the problem i had.

<tbody>
</tbody>
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top