Sum values in one column based on Criteria in Multiple Columns

**jcjc7** · 10-26-2017, 03:55 PM

Hi all,

I want to be able to sum the values of responses to a question based on multiple criteria spread out throughout more than one column.

Please see the following image below:
sumifs cami.JPG

I used the following formula, but it will only sum the values based on the first column in the array
=SUMIF(A:F,"als",G:G)
=SUMIF(A:F,"cerebralpalsy",G:G)
etc...

I've thought of using a SUMIFS by doing =SUMIFS(G:G,A:A,"als",B:B,"als" .. etc.) but I want to see if there is a simpler way to use a SUMIF across multiple columns.

Appreciate the help!

**Sam Capricci** · 10-26-2017, 04:00 PM

Use SUMIFS for that, other options might be sumproduct but sumifs is the usual way to go. Also, instead of using whole columns it will be more efficient if you use ranges, just make sure they are all the same like A2:A2000 you also have to use G2:G2000 etc.

**shukla.ankur281190** · 10-27-2017, 02:05 AM

Row\Col

A

B

C

D

E

F

G

H

I

J

K

L

1

Que1

Que2

Que3

Que4

Que5

Que6

Total

2

Als

1

3

Als

6

4

Als

5

Als

5

6

Als

8

7

Als

5

Unique Value

8

Als

2

Als

9

Als

1

Als

qdq

10

Als

1

2d2

11

Als

5

111

d2

12

Als

6

13

Als

1

14

Als

5

15

Als

4

16

Als

6

17

Als

3

18

qdq

2d2

qdq

d2

2

19

qdq

2d2

4

20

qdq

2d2

8

I11

Formula:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Check the attached file.

Is it what you want ??