Issue with SumIfs function

matija385hr

New Member
Joined
Mar 14, 2020
Messages
20
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,607
Office Version
  1. 365
Platform
  1. Windows
You need to use SUM(SUMIFS(.....)) not just SUMIFS(.....)
 

matija385hr

New Member
Joined
Mar 14, 2020
Messages
20
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 30, 2008
Messages
14,607
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Mar 14, 2020
Messages
20
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Dec 30, 2008
Messages
14,607
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Mar 14, 2020
Messages
20
Office Version
  1. 365
Platform
  1. Windows
Sorry Jason, I removed SUM from formula. Since I added SUM(SUMIFS(...)) it works perfectly.

Thank you once again,

Kind regards,
Matija
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,587
Messages
5,765,300
Members
425,271
Latest member
kristyfinn

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
Top