Summing range of columns in one row based on criteria

[rebech]

Hello,

I have a large table similar to the one below, with first column as ID numbers. I want to select any row based on the ID number and select only a range of data from that row. For example, I want to be able to select data for ID #3 from March to May, which would be 6. SUMIF didn't work for me. Any help would be appreciated!


Jan Feb March April May
1 5 10 15 20 25
2 4 8 12 16 20
3 2 2 2 2 2
4 6 7 8 9 10

 

[rebech]

Sorry bad formatting. Hope this works better..

| Jan Feb March April May
1| 5, 10, 15, 20, 25
2| 4, 8, 12, 16, 20
3| 2, 2, 2, 2, 2
4| 6, 7, 8, 9, 10

 

[Marcelo Branco]

Welcome to Mr Excel forum

Maybe...

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][td="bgcolor: #DCE6F1"]

H​

[/td][td="bgcolor: #DCE6F1"]

I​

[/td][td="bgcolor: #DCE6F1"]

J​

[/td][td="bgcolor: #DCE6F1"]

K​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

ID​

[/td][td]

Jan​

[/td][td]

Feb​

[/td][td]

March​

[/td][td]

April​

[/td][td]

May​

[/td][td][/td][td]

ID​

[/td][td]

First​

[/td][td]

Last​

[/td][td]

Result​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

1​

[/td][td]

5​

[/td][td]

10​

[/td][td]

15​

[/td][td]

20​

[/td][td]

25​

[/td][td][/td][td]

3​

[/td][td]

March​

[/td][td]

May​

[/td][td]

6​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

2​

[/td][td]

4​

[/td][td]

8​

[/td][td]

12​

[/td][td]

16​

[/td][td]

20​

[/td][td][/td][td]

1​

[/td][td]

Feb​

[/td][td]

April​

[/td][td]

45​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

3​

[/td][td]

2​

[/td][td]

2​

[/td][td]

2​

[/td][td]

2​

[/td][td]

2​

[/td][td][/td][td]

4​

[/td][td]

Jan​

[/td][td]

Feb​

[/td][td]

13​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

4​

[/td][td]

6​

[/td][td]

7​

[/td][td]

8​

[/td][td]

9​

[/td][td]

10​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Criteria in columns H:J

Formula in K2 copied down
=SUM(INDEX(B$2:F$5,MATCH(H2,A$2:A$5,0),MATCH(I2,B$1:F$1,0)):INDEX(B$2:F$5,MATCH(H2,A$2:A$5,0),MATCH(J2,B$1:F$1,0)))

Hope this helps

M.

 

[rebech]

This worked! Thank you so much!