Sum up columns based on values (date and time) in more than one column

jeffersone

New Member
Joined
Aug 23, 2024
Messages
2
Office Version
  1. 2021
Platform
  1. Windows
Hello,

I am having some difficulty writing a formula to sum up data based on the value of two or three other columns. I would like for Excel to sum up total number of participants for a certain exam time on a certain date.
In the example below, on the 2nd of September, I would like to sum up the number of participants for the exam 8:00 - 11:00 hours, 8:00 - 12:00 hours, 14:00 - 17:00 hours och 14:00 - 18:00 hours and so on.

Is there a way to build a formula based on this criteria to get a result like on column H. A VBA is ideal but I am thankful for any suggestions even about a formula. If needed I can have duplicates of the date on each row.

1724414784893.png
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Unfortunately I can’t use XL2BB at present, but if you fill the dates to each row, then put this formula in H2
Excel Formula:
=LET(x,UNIQUE(B2:D1000),SORTBY(x,INDEX(x,0,1),1,INDEX(x,0,2),1,INDEX(x,0,3),1))
you will get a spilled array in columns H:J of the unique date/time combinations. Change D1000 in the formula to suit your bottom row.
Then in K2 put the formula
Excel Formula:
=SUMIFS(G$2:G$1000,B$2:B$1000,H2,C$2:C$1000,I2,D$2:D$1000,J2)
and copy down.
 
Upvote 0
Solution
Unfortunately I can’t use XL2BB at present, but if you fill the dates to each row, then put this formula in H2
Excel Formula:
=LET(x,UNIQUE(B2:D1000),SORTBY(x,INDEX(x,0,1),1,INDEX(x,0,2),1,INDEX(x,0,3),1))
you will get a spilled array in columns H:J of the unique date/time combinations. Change D1000 in the formula to suit your bottom row.
Then in K2 put the formula
Excel Formula:
=SUMIFS(G$2:G$1000,B$2:B$1000,H2,C$2:C$1000,I2,D$2:D$1000,J2)
and copy down
Thank you so very much. That did the trick!
 
Upvote 0

Forum statistics

Threads
1,224,595
Messages
6,179,798
Members
452,943
Latest member
Newbie4296

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top