SUMIFS getting mostly correct value when using named ranges, but I need help to get it 100% right

ajnin · Jan 16, 2024

Overview:

I would like to download a monthly CSV of my bank statement and paste it into excel. I then want to work out monthly costs for groups of Items. Example would be supermarkets. In the credit card dumb it might have 3 or 4 different super markets, and in the past I have been using a formula like this do work it out for January

=(SUMIFS(All!$B:$B,All!$A:$A,">=1/1/2019",All!$A:$A,"<=31/1/2019",All!$C:$C,"COLES*")+(SUMIFS(All!$B:$B,All!$A:$A,">=1/1/2019",All!$A:$A,"<=31/1/2019",All!$C:$C,"SAFEWAY*")+(SUMIFS(All!$B:$B,All!$A:$A,">=1/1/2019",All!$A:$A,"<=31/1/2019",All!$C:$C,"ALDI*"))))*-1

This works, but its not very nice and its a pain if I want to add a new supermarket. So I had the idea to create a named range with all the supermarkets and then use that in the formula.

So now I am using the formula

=SUMIFS(New!$B:$B,New!$A:$A,">=1/1/2023",New!$A:$A,"<=31/1/2023",New!$C:$C,Shops)*-1

I have 2 issues
Issue 1 - I have to list the shop 12 times or it does not work each month
Issue 2 - It only picks up the first shop

see below

this is only showing the MOnthly sum of the Coles and not Coles + IGA

TEST - Bills and Budget TEST.xlsx

A

W

1

Year

Super Markets

2

Jan-23

$333.52

3

Feb-23

$257.71

4

Mar-23

$568.16

5

Apr-23

$440.75

6

May-23

$604.50

7

Jun-23

$403.94

8

Jul-23

$410.35

9

Aug-23

$0.00

10

Sep-23

$251.55

11

Oct-23

$466.63

12

Nov-23

$418.66

13

Dec-23

$316.33

2023

Cell Formulas
Range	Formula
W2	W2	=SUMIFS(New!$B:$B,New!$A:$A,">=1/1/2023",New!$A:$A,"<=31/1/2023",New!$C:$C,Shops)*-1
W3	W3	=SUMIFS(New!$B:$B,New!$A:$A,">=1/2/2023",New!$A:$A,"<=28/2/2023",New!$C:$C,Shops)*-1
W4	W4	=SUMIFS(New!$B:$B,New!$A:$A,">=1/3/2023",New!$A:$A,"<=31/3/2023",New!$C:$C,Shops)*-1
W5	W5	=SUMIFS(New!$B:$B,New!$A:$A,">=1/4/2023",New!$A:$A,"<=30/4/2023",New!$C:$C,Shops)*-1
W6	W6	=SUMIFS(New!$B:$B,New!$A:$A,">=1/5/2023",New!$A:$A,"<=31/5/2023",New!$C:$C,Shops)*-1
W7	W7	=SUMIFS(New!$B:$B,New!$A:$A,">=1/6/2023",New!$A:$A,"<=30/6/2023",New!$C:$C,Shops)*-1
W8	W8	=SUMIFS(New!$B:$B,New!$A:$A,">=1/7/2023",New!$A:$A,"<=31/7/2023",New!$C:$C,Shops)*-1
W9	W9	=SUMIFS(New!$B:$B,New!$A:$A,">=1/8/2023",New!$A:$A,"<=31/1/2023",New!$C:$C,Shops)*-1
W10	W10	=SUMIFS(New!$B:$B,New!$A:$A,">=1/9/2023",New!$A:$A,"<=30/9/2023",New!$C:$C,Shops)*-1
W11	W11	=SUMIFS(New!$B:$B,New!$A:$A,">=1/10/2023",New!$A:$A,"<=31/10/2023",New!$C:$C,Shops)*-1
W12	W12	=SUMIFS(New!$B:$B,New!$A:$A,">=1/11/2023",New!$A:$A,"<=30/11/2023",New!$C:$C,Shops)*-1
W13	W13	=SUMIFS(New!$B:$B,New!$A:$A,">=1/12/2023",New!$A:$A,"<=31/12/2023",New!$C:$C,Shops)*-1

Named Ranges
Name	Refers To	Cells
New!_FilterDatabase	=New!$A$1:$D$6651	W2:W13
Shops	=Items!$B$2:$B$25	W2

TEST - Bills and Budget TEST.xlsx

B

1

Coles

2

*Coles*

3

*Coles*

4

*Coles*

5

*Coles*

6

*Coles*

7

*Coles*

8

*Coles*

9

*Coles*

10

*Coles*

11

*Coles*

12

*Coles*

13

*Coles*

14

*IGA*

15

*IGA*

16

*IGA*

17

*IGA*

18

*IGA*

19

*IGA*

20

*IGA*

21

*IGA*

22

*IGA*

23

*IGA*

24

*IGA*

25

*IGA*

Items

If I change it up, now I will get correct Coles for January for Feb i cant work out how it came up with that vaule

TEST - Bills and Budget TEST.xlsx

A

W

1

Year

Super Markets

2

Jan-23

$333.52

3

Feb-23

$310.49

4

Mar-23

$0.00

5

Apr-23

$0.00

6

May-23

$0.00

7

Jun-23

$0.00

8

Jul-23

$0.00

9

Aug-23

$0.00

10

Sep-23

$0.00

11

Oct-23

$0.00

12

Nov-23

$0.00

13

Dec-23

$0.00

2023

Cell Formulas
Range	Formula
W2	W2	=SUMIFS(New!$B:$B,New!$A:$A,">=1/1/2023",New!$A:$A,"<=31/1/2023",New!$C:$C,Shops)*-1
W3	W3	=SUMIFS(New!$B:$B,New!$A:$A,">=1/2/2023",New!$A:$A,"<=28/2/2023",New!$C:$C,Shops)*-1
W4	W4	=SUMIFS(New!$B:$B,New!$A:$A,">=1/3/2023",New!$A:$A,"<=31/3/2023",New!$C:$C,Shops)*-1
W5	W5	=SUMIFS(New!$B:$B,New!$A:$A,">=1/4/2023",New!$A:$A,"<=30/4/2023",New!$C:$C,Shops)*-1
W6	W6	=SUMIFS(New!$B:$B,New!$A:$A,">=1/5/2023",New!$A:$A,"<=31/5/2023",New!$C:$C,Shops)*-1
W7	W7	=SUMIFS(New!$B:$B,New!$A:$A,">=1/6/2023",New!$A:$A,"<=30/6/2023",New!$C:$C,Shops)*-1
W8	W8	=SUMIFS(New!$B:$B,New!$A:$A,">=1/7/2023",New!$A:$A,"<=31/7/2023",New!$C:$C,Shops)*-1
W9	W9	=SUMIFS(New!$B:$B,New!$A:$A,">=1/8/2023",New!$A:$A,"<=31/1/2023",New!$C:$C,Shops)*-1
W10	W10	=SUMIFS(New!$B:$B,New!$A:$A,">=1/9/2023",New!$A:$A,"<=30/9/2023",New!$C:$C,Shops)*-1
W11	W11	=SUMIFS(New!$B:$B,New!$A:$A,">=1/10/2023",New!$A:$A,"<=31/10/2023",New!$C:$C,Shops)*-1
W12	W12	=SUMIFS(New!$B:$B,New!$A:$A,">=1/11/2023",New!$A:$A,"<=30/11/2023",New!$C:$C,Shops)*-1
W13	W13	=SUMIFS(New!$B:$B,New!$A:$A,">=1/12/2023",New!$A:$A,"<=31/12/2023",New!$C:$C,Shops)*-1

Named Ranges
Name	Refers To	Cells
New!_FilterDatabase	=New!$A$1:$D$6651	W2:W13
Shops	=Items!$B$2:$B$25	W2

TEST - Bills and Budget TEST.xlsx

B

1

Coles

2

*Coles*

3

*IGA*

Items

I would like to have all coles and all iga added together for each respective month

the bank extract looks like

TEST - Bills and Budget TEST.xlsx

A

B

C

4360

27/02/2023

-12

COLES 0001

4362

27/02/2023

-92.2

COLES 0001 Area Name

4370

27/02/2023

-21.01

COLES 0001 Area Name

4393

22/02/2023

-8.77

Street name IGA Area Name

4445

14/02/2023

-27.72

COLES 0001 Area Name

4473

13/02/2023

-34.08

COLES EXPRESS 0001 Area Name

4486

9/02/2023

-45.1

COLES 0001 Area Name

4549

1/02/2023

-25.6

COLES 0001 Area Name

4557

31/01/2023

-37.68

COLES 0001 Area Name

4574

30/01/2023

-145.7

COLES 0001 Area Name

4580

30/01/2023

-4.08

Street name IGA Area Name

4687

16/01/2023

-41.42

COLES 0001 Area Name

4698

13/01/2023

-9.67

Street name IGA Area Name

4699

13/01/2023

-9.67

Street name IGA Area Name

4730

9/01/2023

-13.04

COLES 0001 Area Name

4740

9/01/2023

-8.97

Street name IGA Area Name

4741

9/01/2023

-4.55

Street name IGA Area Name

4764

4/01/2023

-80.33

COLES 0001 Area Name

4781

3/01/2023

-15.35

COLES 0001 Area Name

4798

3/01/2023

-0.98

Street name IGA Area Name

4799

3/01/2023

-12.41

Street name IGA Area Name

New

Fluff · Jan 16, 2024

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

ajnin · Jan 16, 2024

Fluff said:
What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Thank you. That is now updated.

Fluff · Jan 16, 2024

Thanks for that.

How about

Excel Formula:

=-sumproduct(SUMIFS(New!$B:$B,New!$A:$A,">=1/1/2023",New!$A:$A,"<=31/1/2023",New!$C:$C,Shops))

ajnin · Jan 17, 2024

Fluff said:
Thanks for that.
How about

Excel Formula:

=-sumproduct(SUMIFS(New!$B:$B,New!$A:$A,">=1/1/2023",New!$A:$A,"<=31/1/2023",New!$C:$C,Shops))

Thank you. This really does help. I have applied it to January and February. It gives the correct value for Jan and Feb if I only have 1 item listed in Shops. If I add a second item Jan is correct, but I Feb is wrong. I cant even work out how its getting this vaule.

Jan Coles 333.52 (Correct)
Jan IGA 50.33
Jan Total 383.85

Feb Coles 257.71 (Correct)
Feb IGA 8.77
Feb Total 266.48

TEST - Bills and Budget TEST.xlsx

A

W

1

Year

Super Markets

2

Jan-23

$333.52

3

Feb-23

$257.71

2023

Cell Formulas
Range	Formula
W2	W2	= -SUMPRODUCT(SUMIFS(New!$B:$B,New!$A:$A,">=1/1/2023",New!$A:$A,"<=31/1/2023",New!$C:$C,Shops))
W3	W3	= -SUMPRODUCT(SUMIFS(New!$B:$B,New!$A:$A,">=1/2/2023",New!$A:$A,"<=28/2/2023",New!$C:$C,Shops))

Named Ranges
Name	Refers To	Cells
New!_FilterDatabase	=New!$A$1:$D$6651	W2:W3
Shops	=Items!$B$2:$B$24	W2:W3

List of items in "shops" zz is just filler

TEST - Bills and Budget TEST.xlsx

B

1

Super Markets

2

*Coles*

3

zz

4

zz

5

zz

Items

SO when its like this everything is right. Once I add in *IGA* Jan has the right vaule, but Feb is wrong. I am expecting it to be Total 266.48

TEST - Bills and Budget TEST.xlsx

A

W

1

Year

Super Markets

2

Jan-23

$383.85

3

Feb-23

$568.20

2023

Cell Formulas
Range	Formula
W2	W2	= -SUMPRODUCT(SUMIFS(New!$B:$B,New!$A:$A,">=1/1/2023",New!$A:$A,"<=31/1/2023",New!$C:$C,Shops))
W3	W3	= -SUMPRODUCT(SUMIFS(New!$B:$B,New!$A:$A,">=1/2/2023",New!$A:$A,"<=28/2/2023",New!$C:$C,Shops))

Named Ranges
Name	Refers To	Cells
New!_FilterDatabase	=New!$A$1:$D$6651	W2:W3
Shops	=Items!$B$2:$B$24	W2:W3

TEST - Bills and Budget TEST.xlsx

B

1

Super Markets

2

*Coles*

3

*IGA*

4

zz

5

zz

Items

Raw Data

TEST - Bills and Budget TEST.xlsx

A

B

C

4360

27/02/2023

-12

COLES 0001

4362

27/02/2023

-92.2

COLES 0001 Area Name

4370

27/02/2023

-21.01

COLES 0001 Area Name

4393

22/02/2023

-8.77

Street name IGA Area Name

4445

14/02/2023

-27.72

COLES 0001 Area Name

4473

13/02/2023

-34.08

COLES EXPRESS 0001 Area Name

4486

9/02/2023

-45.1

COLES 0001 Area Name

4549

1/02/2023

-25.6

COLES 0001 Area Name

4557

31/01/2023

-37.68

COLES 0001 Area Name

4574

30/01/2023

-145.7

COLES 0001 Area Name

4580

30/01/2023

-4.08

Street name IGA Area Name

4687

16/01/2023

-41.42

COLES 0001 Area Name

4698

13/01/2023

-9.67

Street name IGA Area Name

4699

13/01/2023

-9.67

Street name IGA Area Name

4730

9/01/2023

-13.04

COLES 0001 Area Name

4740

9/01/2023

-8.97

Street name IGA Area Name

4741

9/01/2023

-4.55

Street name IGA Area Name

4764

4/01/2023

-80.33

COLES 0001 Area Name

4781

3/01/2023

-15.35

COLES 0001 Area Name

4798

3/01/2023

-0.98

Street name IGA Area Name

4799

3/01/2023

-12.41

Street name IGA Area Name

New

Fluff · Jan 18, 2024

With that data I get 266.48 for Feb. Are you sure that there aren't any other rows for Feb that have IGA somewhere in the cell?

ajnin · Feb 4, 2024

Fluff said:
With that data I get 266.48 for Feb. Are you sure that there aren't any other rows for Feb that have IGA somewhere in the cell?

I am rechecking, but I can see anything. I will make a new sheet

ajnin · Feb 4, 2024

Fluff said:
With that data I get 266.48 for Feb. Are you sure that there aren't any other rows for Feb that have IGA somewhere in the cell?

Thank you @Fluff it was user error.

the formula you showed me works perfectly !!!

Thank you very much !!!

Fluff · Feb 4, 2024

Glad it's working & thanks for the feedback.

SUMIFS getting mostly correct value when using named ranges, but I need help to get it 100% right

ajnin

New Member

Excel Facts

Fluff

MrExcel MVP, Moderator

ajnin

New Member

Fluff

MrExcel MVP, Moderator

ajnin

New Member

Fluff

MrExcel MVP, Moderator

ajnin

New Member

ajnin

New Member

Fluff

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

SUMIFS getting mostly correct value when using named ranges, but I need help to get it 100% right

New Member

Excel Facts

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

New Member

New Member

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock