Sumifs and Index match match

CascadeDiver

Board Regular
Joined
Apr 10, 2020
Messages
53
Office Version
  1. 365
Platform
  1. Windows
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.

Capture.PNG


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

Some videos you may like

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
Joined
Aug 18, 2015
Messages
10,328
How about:

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.
 

CascadeDiver

Board Regular
Joined
Apr 10, 2020
Messages
53
Office Version
  1. 365
Platform
  1. Windows
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
 

CascadeDiver

Board Regular
Joined
Apr 10, 2020
Messages
53
Office Version
  1. 365
Platform
  1. Windows
How about:

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
 

Watch MrExcel Video

Forum statistics

Threads
1,111,703
Messages
5,541,308
Members
410,545
Latest member
Upsindustrial20
Top