Sumifs throwing # Value error

snchandan · May 31, 2023

The following formula throws up # Value error
=SUMIFS(sheet1!$B17:$XFD58,sheet1!$B8:$XFD8,sheet2!D4,sheet1!$B7:$XFD7,"<>Y",sheet1!A16:A58,sheet2!A3)

where as the following works fine.
=SUMIFS(sheet1!$B17:$XFD17,sheet1!$B8:$XFD8,sheet2!D4,sheet1!$B7:$XFD7,"<>Y")

please suggest what is wrong when adding a 3rd criteria.

Fluff · May 31, 2023

Hi & welcome to MrExcel.
You cannot use sumifs when the ranges are not the same size. Try

Excel Formula:

=SUMPRODUCT((Sheet1!$B8:$XFD8=Sheet2!D4)*(Sheet1!$B7:$XFD7<>"Y")*(Sheet1!A16:A58=Sheet2!A3)*(Sheet1!$B16:$XFD58))

snchandan · May 31, 2023

Hello Fluff,

Thanks for the warm welcome and the suggestion.

I am getting same #Value error with the sumproduct too with 3 criteria.

with 2 criteria as follows works fine.
=SUMPRODUCT((Sheet1!$B8:$XFD8=Sheet2!D4)*(Sheet1!$B7:$XFD7<>"Y")*(Sheet1!$B17:$XFD17))

please suggest other solutions.

awoohaw · May 31, 2023

snchandan said:
Hello Fluff,

Thanks for the warm welcome and the suggestion.

I am getting same #Value error with the sumproduct too with 3 criteria.

with 2 criteria as follows works fine.
=SUMPRODUCT((Sheet1!$B8:$XFD8=Sheet2!D4)*(Sheet1!$B7:$XFD7<>"Y")*(Sheet1!$B17:$XFD17))

please suggest other solutions.

I don' think you understood Fluffs comment.
I'm not sure what your formula with 3 criteria is because you didn't display it.
Also, is your data really 16000 columns wide?

try this:

i've tried to create your data on one sheet and also shrunk it up. I have no idea what column A contains. but I guess row 8 has a date, so I used 1st of months.
and for formula transparency I put the Y in a cell and reference that also.

This is what i think you want in the first formula that gives you errors:

Mr excel questions 39.xlsm

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

1

2

Index?

Date

Y/N

Result

3

03/21

Y

9

4

5

6

7

Y

N

Y

N

Y

N

Y

N

Y

N

Y

N

Y

8

9

10

16

17

1

8

1

6

2

4

6

4

6

3

4

9

1

8

5

9

10

8

2

8

6

9

18

2

5

1

8

4

6

4

1

8

2

3

7

4

3

1

3

2

8

1

6

4

19

3

4

2

9

6

7

8

1

8

5

7

6

4

9

3

1

8

3

8

10

7

2

20

4

6

2

5

1

2

1

8

7

9

6

2

6

7

1

10

1

7

5

8

2

6

21

5

2

6

4

7

3

4

7

3

10

1

3

4

5

9

1

5

2

6

1

10

4

1

7

22

6

2

10

6

7

8

4

1

7

1

3

10

3

9

8

4

10

3

2

4

5

1

23

7

9

6

4

1

5

6

4

7

10

2

3

2

10

3

6

9

1

8

24

8

4

10

5

1

6

8

4

1

9

3

7

1

9

3

9

4

5

1

2

1

25

9

1

7

9

7

2

7

8

3

8

1

4

9

6

3

9

6

8

2

10

8

26

10

4

6

10

4

1

6

2

3

9

10

5

10

1

7

1

4

9

2

3

10

7

27

11

10

3

10

3

2

1

4

3

6

4

8

10

6

10

3

6

3

4

10

9

28

12

9

8

7

1

7

4

7

4

3

4

3

4

5

7

1

4

9

2

9

6

1

9

29

13

4

9

2

1

8

4

2

6

1

5

7

5

1

10

9

4

3

7

10

2

1

10

30

14

3

2

10

9

6

5

1

8

1

2

7

2

10

6

1

4

7

5

10

3

31

15

6

10

1

8

1

9

3

2

1

10

4

10

4

8

2

9

5

8

4

10

4

9

3

32

16

6

4

2

6

10

8

1

10

5

2

9

7

6

7

6

7

1

2

5

9

33

17

6

8

4

7

2

8

3

4

10

5

4

6

4

7

10

1

8

9

10

34

18

8

9

5

10

1

3

8

4

6

1

10

1

5

6

5

4

9

35

19

1

4

5

6

3

5

7

5

8

7

2

3

7

2

8

5

8

10

4

36

20

10

8

9

6

10

9

3

9

8

10

7

9

4

1

9

6

9

8

6

4

9

37

21

1

7

3

2

10

2

10

2

4

2

10

6

9

2

10

8

3

5

4

10

4

38

22

4

1

5

7

4

9

1

6

7

10

2

9

4

5

1

9

6

3

6

1

4

9

39

23

7

1

4

9

2

10

4

3

5

7

6

9

2

9

8

9

4

5

3

9

40

24

10

4

10

1

10

9

6

1

8

5

8

6

3

4

3

2

8

9

4

41

25

7

8

3

5

1

7

10

5

9

1

4

9

7

8

4

10

2

3

6

8

42

26

10

1

10

9

3

8

6

9

3

9

7

6

1

6

9

5

3

8

5

6

10

6

8

43

27

6

7

3

2

9

5

4

10

1

10

9

5

6

3

9

10

7

4

5

8

44

28

1

6

10

2

8

2

6

5

7

4

8

6

9

2

5

1

8

1

6

8

10

9

3

45

29

4

5

9

1

10

6

8

1

6

7

6

7

9

7

5

2

3

1

5

8

10

46

30

5

9

1

8

7

4

5

6

3

9

2

5

2

6

9

5

3

7

10

6

2

9

47

31

8

2

6

4

7

8

7

3

8

9

4

2

10

2

9

5

3

2

10

6

2

48

32

7

2

4

9

10

7

6

9

1

6

9

10

5

4

7

1

5

10

5

49

33

2

8

1

4

5

7

9

3

6

4

1

8

5

4

2

6

9

5

8

6

5

50

34

4

7

8

1

8

5

6

4

10

4

2

3

10

5

7

5

3

1

5

4

9

51

35

2

1

10

7

3

4

8

4

9

1

8

2

9

7

6

1

8

6

8

4

2

52

36

4

3

2

5

9

4

10

4

1

5

1

4

6

5

6

7

6

8

10

7

1

9

53

37

8

4

2

5

3

1

8

2

4

6

1

3

5

7

2

10

1

2

7

4

54

38

1

5

9

10

8

1

3

4

3

2

10

3

8

9

10

4

6

3

8

10

8

55

39

8

3

1

10

5

6

9

7

6

8

1

6

10

9

7

10

1

3

10

6

10

1

56

40

8

1

9

2

3

5

7

10

6

1

9

6

4

2

10

7

9

1

3

2

9

57

41

8

7

4

9

1

7

4

9

7

5

3

10

3

10

4

2

1

7

5

3

4

58

42

5

4

1

4

8

5

6

3

2

3

2

8

2

10

6

5

7

3

4

9

8

59

snchandan

Cell Formulas
Range	Formula
J3	J3	=SUMPRODUCT( (B17:X58)* (--(B8:X8=D3))* (A3=A17:A58) * (G3=B7:X7))
B8	B8	=DATE(2021,1,1)
C8:X8	C8	=EDATE(B8,1)

awoohaw · May 31, 2023

if the above doesn't work then you really need to post your data using XL2BB add in (Link Below). NOTE: Images will not help!
Note (if what I call is index is a user or product that repeats throughout column A the above formula will still work).
(the double negative portion of the formula can probably be taken out).

snchandan · Jun 1, 2023

awoohaw said:
I don' think you understood Fluffs comment.
I'm not sure what your formula with 3 criteria is because you didn't display it.
Also, is your data really 16000 columns wide?

try this:

i've tried to create your data on one sheet and also shrunk it up. I have no idea what column A contains. but I guess row 8 has a date, so I used 1st of months.
and for formula transparency I put the Y in a cell and reference that also.

This is what i think you want in the first formula that gives you errors:

Mr excel questions 39.xlsm
A B C D E F G H I J K L M N O P Q R S T U V W X Y
1
2 Index? Date Y/N Result
3 3 03/21 Y 9
4
5
6
7 Y N Y Y Y Y Y N Y N N N Y Y N Y N Y N N N Y Y
8 01/21 02/21 03/21 04/21 05/21 06/21 07/21 08/21 09/21 10/21 11/21 12/21 01/22 02/22 03/22 04/22 05/22 06/22 07/22 08/22 09/22 10/22 11/22
9
10
16
17 1 1 8 1 6 2 4 6 4 6 3 4 9 1 8 5 9 10 8 2 8 6 6 9
18 2 5 1 1 8 4 4 6 4 1 8 2 3 7 4 3 3 1 3 2 8 1 6 4
19 3 4 2 9 6 7 8 1 8 5 7 7 6 4 9 3 1 8 3 3 8 10 7 2
20 4 6 2 5 1 2 2 1 8 7 9 6 2 6 7 1 10 1 7 5 8 2 6 6
21 5 2 6 4 7 3 4 7 3 10 1 3 4 5 9 1 5 2 6 1 10 4 1 7
22 6 2 10 6 7 8 4 1 7 1 3 10 3 9 9 8 4 10 3 2 4 5 1 1
23 7 9 6 6 6 4 1 5 6 4 4 7 10 2 3 2 2 10 3 6 6 9 1 8
24 8 4 10 5 1 6 8 4 1 1 9 3 7 1 9 3 3 3 9 4 5 1 2 1
25 9 9 9 1 1 7 9 7 2 7 8 3 8 1 4 9 6 3 9 6 8 2 10 8
26 10 10 4 6 10 4 1 6 2 2 3 9 10 5 10 1 7 1 4 9 2 3 10 7
27 11 10 3 10 3 3 2 1 4 3 6 4 4 8 10 6 10 3 6 6 3 4 10 9
28 12 9 8 7 1 7 4 7 4 3 4 3 4 5 7 1 4 9 2 2 9 6 1 9
29 13 4 9 9 2 1 8 4 2 6 1 5 7 5 1 10 9 4 3 7 10 2 1 10
30 14 3 2 10 9 6 5 1 1 8 1 2 2 7 2 10 6 6 1 4 7 5 10 3
31 15 6 10 1 8 1 9 3 2 1 10 4 10 4 8 2 9 5 8 4 10 4 9 3
32 16 6 4 2 6 10 8 8 1 10 5 5 2 9 7 7 6 7 6 7 1 2 5 9
33 17 6 8 4 7 2 8 3 4 10 5 4 4 4 6 6 4 7 7 10 1 8 9 10
34 18 8 9 5 10 1 3 8 8 4 6 1 1 10 1 1 5 6 5 5 4 9 9 9
35 19 1 4 5 5 6 3 5 7 5 8 7 2 3 7 2 8 5 8 8 8 10 10 4
36 20 10 8 9 6 6 10 9 3 9 8 8 10 7 9 4 1 9 6 9 8 6 4 9
37 21 1 7 3 2 10 2 10 2 4 2 10 6 9 2 2 2 10 8 3 5 4 10 4
38 22 4 4 1 5 7 4 9 1 6 7 10 2 9 4 5 1 9 6 3 6 1 4 9
39 23 7 7 1 4 9 2 10 4 3 5 7 7 6 9 2 9 8 9 4 4 5 3 9
40 24 10 4 10 1 10 9 6 6 1 1 8 5 8 6 3 4 3 2 2 8 9 4 4
41 25 7 8 3 5 1 7 10 5 9 1 4 9 7 7 8 4 4 10 10 2 3 6 8
42 26 10 1 10 9 3 8 6 9 3 9 7 6 1 6 9 5 3 8 5 6 10 6 8
43 27 6 7 7 3 2 9 5 4 4 10 1 1 10 9 5 6 3 9 10 7 4 5 8
44 28 1 6 10 2 8 2 6 5 7 4 8 6 9 2 5 1 8 1 6 8 10 9 3
45 29 4 5 9 1 10 6 8 1 6 7 6 7 7 9 7 5 5 2 3 1 5 8 10
46 30 5 9 9 1 8 7 4 5 6 3 9 2 5 2 6 9 5 3 7 10 6 2 9
47 31 8 2 6 4 7 8 7 3 8 9 4 4 2 10 2 9 5 5 3 2 10 6 2
48 32 7 2 4 9 9 10 7 6 9 1 6 6 9 10 5 4 4 4 7 1 5 10 5
49 33 2 8 1 4 5 7 9 9 3 6 6 4 1 8 5 4 2 6 9 5 8 6 5
50 34 4 7 8 1 8 5 6 4 10 4 2 3 10 5 7 5 3 3 1 5 5 4 9
51 35 2 2 1 10 7 3 4 8 4 9 1 8 2 9 9 7 6 1 8 6 8 4 2
52 36 4 3 2 5 9 4 10 4 1 5 1 4 6 5 6 7 7 6 8 10 7 1 9
53 37 8 4 2 5 3 1 1 8 8 2 4 6 1 3 5 7 2 10 1 1 2 7 4
54 38 1 5 9 10 8 1 3 4 3 2 10 3 8 9 10 4 6 6 3 8 10 10 8
55 39 8 3 1 10 5 6 9 7 6 8 1 6 10 9 7 10 1 3 3 10 6 10 1
56 40 8 1 9 2 3 5 7 10 6 1 9 6 4 2 10 7 9 1 1 3 3 2 9
57 41 8 7 7 4 9 1 7 7 4 9 7 5 3 10 3 10 4 2 1 7 5 3 4
58 42 5 4 1 4 8 5 6 3 2 3 2 8 2 2 2 10 6 5 7 3 4 9 8
59
snchandan
Cell Formulas
Range Formula
J3 J3 =SUMPRODUCT( (B17:X58)* (--(B8:X8=D3))* (A3=A17:A58) * (G3=B7:X7))
B8 B8 =DATE(2021,1,1)
C8:X8 C8 =EDATE(B8,1)

Thanks a ton for the detailed explanation. It works fine now.
appreciate your support.

Fluff · Jun 1, 2023

Glad we could help & thanks for the feedback.

awoohaw · Jun 1, 2023

snchandan said:
Thanks a ton for the detailed explanation. It works fine now.
appreciate your support.

Best wishes.

Sumifs throwing # Value error

snchandan

New Member

Excel Facts

Fluff

MrExcel MVP, Moderator

snchandan

New Member

awoohaw

Well-known Member

awoohaw

Well-known Member

snchandan

New Member

Fluff

MrExcel MVP, Moderator

awoohaw

Well-known Member

Similar threads

Forum statistics

Share this page

Sumifs throwing # Value error

New Member

Excel Facts

MrExcel MVP, Moderator

New Member

Well-known Member

Well-known Member

New Member

MrExcel MVP, Moderator

Well-known Member

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