Issue with SumIfs function

[matija385hr]

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.

Can someone please help me with this issue?

Kind regards,
Matija

 

[jasonb75]

You need to use SUM(SUMIFS(.....)) not just SUMIFS(.....)

 

[matija385hr]

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]

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]

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]

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]

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

Thank you once again,

Kind regards,
Matija