# Sumifs and Index match match

##### Board Regular
Hello,

I'm having a difficult time writing a formula that acts like Index match match along with sumifs.

I have a table with a column of dates and 4 columns with names. To the right of each name column is a number.
My end goal is to sum the numbers adjacent to each name and date. Those numbers are then placed in a different table that has Dates, Names, and Sums.

In the example I have some dates skipped and some dates duplicated. Shown in the right graph is the desired outcome.

I've tried doing a Power Query to achieve these results however I got a bit lost in that attempt.

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

#### Eric W

##### MrExcel MVP

Book1
ABCDEFGHIJKLMNOPQRSTU
1
2DatePerson 1P1#Person 2P2#Person 3P3#Person 4P4#DateJamesLucasBenjaminMasonOliverLiamElijahWilliamNoahEthan
310/1/2020Liam10Lucas8Oliver3Mason310/1/2020 8 3310
410/2/2020Noah6Ethan2Lucan5William610/2/2020       662
510/3/2020Oliver4Liam1Elijah7Lucas110/3/2020 1  417
610/4/2020Willaim9Elijah9Mason9Noah210/4/2020   194 10 210
710/4/2020Elijah1Mason10Ethan10Oliver410/5/2020
810/6/2020Mason5Benjamin7Noah4James710/6/20207 75    4
910/7/2020Benjamin8James6Liam8Elijah910/7/20206 8  89
1010/8/2020Lucas3Noah3William6Benjamin1010/8/20202312    1136
1110/8/2020James2William5Benjamin2Ethan610/9/2020
1210/10/2020Ethan7Oliver4James1Liam510/10/20201   45   7
Sheet5
Cell Formulas
RangeFormula
L3:U12L3=SUM(IF(\$A\$3:\$A\$12=\$K3,IF(\$B\$3:\$H\$12=L\$2,\$C\$3:\$I\$12)))
Press CTRL+SHIFT+ENTER to enter array formulas.

Also, please consider using the XL2BB add-in to display your sample sheet. See the link in the response box. It makes it much easier for the helpers to start working with your data.

##### Board Regular
Yes that certainly worked. I had 4 different sumifs going thinking there has got to be an easier way.

I will take a look at xl2bb .

Thank you very much

##### Board Regular

Book1
ABCDEFGHIJKLMNOPQRSTU
1
2DatePerson 1P1#Person 2P2#Person 3P3#Person 4P4#DateJamesLucasBenjaminMasonOliverLiamElijahWilliamNoahEthan
310/1/2020Liam10Lucas8Oliver3Mason310/1/2020 8 3310
410/2/2020Noah6Ethan2Lucan5William610/2/2020       662
510/3/2020Oliver4Liam1Elijah7Lucas110/3/2020 1  417
610/4/2020Willaim9Elijah9Mason9Noah210/4/2020   194 10 210
710/4/2020Elijah1Mason10Ethan10Oliver410/5/2020
810/6/2020Mason5Benjamin7Noah4James710/6/20207 75    4
910/7/2020Benjamin8James6Liam8Elijah910/7/20206 8  89
1010/8/2020Lucas3Noah3William6Benjamin1010/8/20202312    1136
1110/8/2020James2William5Benjamin2Ethan610/9/2020
1210/10/2020Ethan7Oliver4James1Liam510/10/20201   45   7
Sheet5
Cell Formulas
RangeFormula
L3:U12L3=SUM(IF(\$A\$3:\$A\$12=\$K3,IF(\$B\$3:\$H\$12=L\$2,\$C\$3:\$I\$12)))
Press CTRL+SHIFT+ENTER to enter array formulas.

I'm struggling changing this formula around to fit a slightly different parameter. How would I go about changing to Summing everything in the month and year?

I actually have this done already except I needed to make a helper column that displays the month of the date. I am trying to avoid the helper column. So in the case the K column displayed the words January - December.

I did find something helpful for a countifs but I haven't been successful at adapting it
=COUNTIFS(Table1[Date],">="&[@Month],Table1[Date],"<="&EOMONTH([@Month],0)
The Month column is a date 10/1/2020 formatted to ready October to provide the month name as a visual

Replies
2
Views
105
Replies
2
Views
114
Replies
6
Views
144
Replies
5
Views
101
Replies
1
Views
94