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