Sumifs limit

[peter8848]

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

 

[bobsan42]

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

 

[steve the fish]

How about a sumproduct?

=SUMPRODUCT(--(NOT((A1:A5="apple")*(B1:B5="yellow"))),--(C1:C5<>"Thursday"),D1:D5)

 

[Aladin Akyurek]

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

 

[peter8848]

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

 

[peter8848]

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

 

[peter8848]

Hi Aladin,

Thanks for your reply. Please find the below:

1. we have product hierarchy code like [TABLE="width: 116"]
<tbody>[TR]
[TD="class: xl63, width: 116"]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.[/TD]
[/TR]
</tbody>[/TABLE]