# Issue with SumIfs function

#### matija385hr

##### New Member
Hi all,

I need help with SumIfs function. I have report where should excel sum values: if date between first and last, if order status is different, if customer name is different than those entered, etc... but I can't seem to get it to work.

I tried with array, but then for customer name is only taking the first entered in array, and if I create different criteria in range I get value as 0.

Here is formula with criteria:
=IF(TODAY()<=DATE(J\$2;MONTH(DATEVALUE(\$A21&1));1);"";SUMIFS(NARUDŽBE!R:R;NARUDŽBE!R:R;">0";NARUDŽBE!C:C;"<>Na čekanju";NARUDŽBE!C:C;"<>Stornirano";NARUDŽBE!C:C;"<>Potreban ispravak";NARUDŽBE!E:E;VP_KUPCI!\$A\$2;NARUDŽBE!E:E;VP_KUPCI!\$A\$3;NARUDŽBE!E:E;VP_KUPCI!\$A\$4;NARUDŽBE!E:E;VP_KUPCI!\$A\$5;NARUDŽBE!E:E;VP_KUPCI!\$A\$6;NARUDŽBE!E:E;VP_KUPCI!\$A\$7;NARUDŽBE!E:E;VP_KUPCI!\$A\$8;NARUDŽBE!E:E;VP_KUPCI!\$A\$9;NARUDŽBE!B:B;">="&DATE(J\$2;MONTH(DATEVALUE(\$A21&1));1);NARUDŽBE!B:B;"<="&DATE(J\$2;MONTH(DATEVALUE(\$A21&1));31)))

where in NARUDŽBE!E:E;VP_KUPCI\$A\$2, etc are customer names that should be summed, but I get value 0.

Here is formula with array:
=IF(TODAY()<=DATE(J\$2;MONTH(DATEVALUE(\$A20&1));1);"";SUMIFS(NARUDŽBE!R:R;NARUDŽBE!R:R;">0";NARUDŽBE!C:C;"<>Na čekanju";NARUDŽBE!C:C;"<>Stornirano";NARUDŽBE!C:C;"<>Potreban ispravak";NARUDŽBE!E:E;{"Harvey Norman Croatia d.o.o.";"BIOFLEX BIOSAN d.o.o.";"Hilding Anders d.o.o.";"Nord mobil d.o.o.";"NORD PRODUKT d. o. o.";"VALAMAR RIVIERA d.d."};NARUDŽBE!B:B;">="&DATE(J\$2;MONTH(DATEVALUE(\$A20&1));1);NARUDŽBE!B:B;"<="&DATE(J\$2;MONTH(DATEVALUE(\$A20&1));31)))

where in NARUDŽBE!E:E;{"Harvey Norman Croatia d.o.o.";"BIOFLEX BIOSAN d.o.o."}... are customer names that should be summed up, but Sum is working only for first customer - in this case Harvey Norman Croatia d.o.o., and others are ignored in summing.

Kind regards,
Matija

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

#### jasonb75

##### Well-known Member
You need to use SUM(SUMIFS(.....)) not just SUMIFS(.....)

#### matija385hr

##### New Member
Hi Jason,

Thank you, that helped. I didn't use array, but criteria range and criteria method. That worked when I want to sum values that are different than cell values on other sheet (i'm using NARUDZBE!E:E;"<>"&VP_KUPCI!A2, etc), but that doesn't work when I want to sum values that are matched to cell values (NARUDZBE!E:E;VP_KUPCI!A2)

Here is working formula when criteria should be different than cell values:
=IF(TODAY()<=DATE(J\$2;MONTH(DATEVALUE(\$A21&1));1);"";SUM(SUMIFS(NARUDŽBE!R:R;NARUDŽBE!R:R;">0";NARUDŽBE!C:C;"<>Na čekanju";NARUDŽBE!C:C;"<>Stornirano";NARUDŽBE!C:C;"<>Potreban ispravak";NARUDŽBE!E:E;"<>"&VP_KUPCI!A2;NARUDŽBE!E:E;"<>"&VP_KUPCI!A3;NARUDŽBE!E:E;"<>"&VP_KUPCI!A4;NARUDŽBE!E:E;"<>"&VP_KUPCI!A5;NARUDŽBE!E:E;"<>"&VP_KUPCI!A6;NARUDŽBE!E:E;"<>"&VP_KUPCI!A7;NARUDŽBE!E:E;"<>"&VP_KUPCI!A8;NARUDŽBE!E:E;"<>"&VP_KUPCI!A9;NARUDŽBE!E:E;"<>"&VP_KUPCI!A10;NARUDŽBE!E:E;"<>"&VP_KUPCI!A11;NARUDŽBE!E:E;"<>"&VP_KUPCI!A12;NARUDŽBE!E:E;"<>"&VP_KUPCI!A13;NARUDŽBE!E:E;"<>"&VP_KUPCI!A14;NARUDŽBE!E:E;"<>"&VP_KUPCI!A15;NARUDŽBE!B:B;">="&DATE(J\$2;MONTH(DATEVALUE(\$A21&1));1);NARUDŽBE!B:B;"<="&DATE(J\$2;MONTH(DATEVALUE(\$A21&1));31))))

Here is formula which doesn't work when I want to sum values that meet criteria:
=IF(TODAY()<=DATE(J\$2;MONTH(DATEVALUE(\$A21&1));1);"";SUM(SUMIFS(NARUDŽBE!R:R;NARUDŽBE!R:R;">0";NARUDŽBE!C:C;"<>Na čekanju";NARUDŽBE!C:C;"<>Stornirano";NARUDŽBE!C:C;"<>Potreban ispravak";NARUDŽBE!E:E;VP_KUPCI!A2;NARUDŽBE!E:E;VP_KUPCI!A3;NARUDŽBE!E:E;VP_KUPCI!A4;NARUDŽBE!E:E;VP_KUPCI!A5;NARUDŽBE!E:E;VP_KUPCI!A6;NARUDŽBE!E:E;VP_KUPCI!A7;NARUDŽBE!E:E;VP_KUPCI!A8;NARUDŽBE!E:E;VP_KUPCI!A9;NARUDŽBE!E:E;VP_KUPCI!A10;NARUDŽBE!E:E;VP_KUPCI!A11;NARUDŽBE!E:E;VP_KUPCI!A12;NARUDŽBE!E:E;VP_KUPCI!A13;NARUDŽBE!E:E;VP_KUPCI!A14;NARUDŽBE!E:E;VP_KUPCI!A15;NARUDŽBE!B:B;">="&DATE(J\$2;MONTH(DATEVALUE(\$A21&1));1);NARUDŽBE!B:B;"<="&DATE(J\$2;MONTH(DATEVALUE(\$A21&1));31))))

What am I doing wrong in other second formula? Sum value is 0

Thank you,
Kind regards,
Matija

#### jasonb75

##### Well-known Member
When you use criteria of "<>" then each must be in a separate [Criteria range, Criteria] pair as you have done, in this simple example C:C is checked twice, once for each criteria to exclude. SUMIFS(R:R;C:C;"<>something";C:C;"<>something else")

When you are looking at criteria that are equal, that method would look for rows in column C that are equal to both criteria, not rows that are equal to either or them. To make that work, the entire range needs to be set to a single [criteria range, criteria] pair.

I think that I have edited your formula correctly but have not been able to test it. I'm not entirely sure but I believe that you will need to enter this as a legacy array formula by pressing Ctrl Shift Enter in order to prevent the formula from 'spilling' as a dynamic array.

=IF(TODAY()<=DATE(J\$2;MONTH(DATEVALUE(\$A21&1));1);"";SUM(SUMIFS(NARUDŽBE!R:R;NARUDŽBE!R:R;">0";NARUDŽBE!C:C;"<>Na čekanju";NARUDŽBE!C:C;"<>Stornirano";NARUDŽBE!C:C;"<>Potreban ispravak";NARUDŽBE!E:E;VP_KUPCI!A2:A15;NARUDŽBE!B:B;">="&DATE(J\$2;MONTH(DATEVALUE(\$A21&1));1);NARUDŽBE!B:B;"<="&DATE(J\$2;MONTH(DATEVALUE(\$A21&1));31))))

#### matija385hr

##### New Member
Thank you for your quick response.

I tried it, but it's summing only values that are first in that range - in this example VP_KUPCI!A2

#### jasonb75

##### Well-known Member
It should work fine as long as it has been array confirmed.
Try using SUMPRODUCT instead of SUM, if that does the same thing then it would suggest that the data is not matching as expected. Additional spaces in text are a common cause of this.

#### matija385hr

##### New Member
Sorry Jason, I removed SUM from formula. Since I added SUM(SUMIFS(...)) it works perfectly.

Thank you once again,

Kind regards,
Matija

Replies
4
Views
572
Replies
9
Views
1K
Replies
8
Views
914
Replies
2
Views
210
Replies
9
Views
407

1,185,983
Messages
5,955,139
Members
438,182
Latest member
karamarrott

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

### Which adblocker are you using?

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

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