Sum function formula or alternate

Khatri1001

New Member
Joined
Dec 22, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi,
I am new to this forum and would like to receive help with a formula for particular set-up.
My spreadsheet consists for committee member time sheet that includes attendance Time-In and Time-out and a column that calculates Total Hours for each event.
There are 6 different meeting events that the committee member attends.

The sum formula I would like help with is that, in the Activity column (Col c) with "Council Meeting", to add up all the Total Hours (Col F) for all "activities" in Column H, from that "Council Meeting" and up to all the other meetings (non-"Council meetings"). This summation to be repeated for each occurance of "Council Meeting".
I have colored the band that needs to be added.
My formula works for the first set, but fail as it goes down the table.

Thanks for you help.
Razak

Per Diem Tracker v2.1-for Peneet.xlsm
BCDEFGHI
1Time Sheet
2
3Member Details:NameEmailPhoneAppointment Date
4SupermanDecember 14, 2021
5
6Daily Per Diem HoursTotal Hours WorkedTotal Work DaysAppointment Term End Date
77.2540.255.55December 13, 2022
8
9
10DateActivityTime InTime OutTotal HoursCompensationColumn1Invoice #
11November 1, 2021MPFAC Meeting Prep8:00 AM3:00 PM7.00$144.83FALSE
12November 10, 2021OHT Subcommittee8:00 AM3:15 PM7.25$150.00FALSE
13December 2, 2021Council Meeting1:00 PM3:00 PM2.00$41.3816.25ü works
14December 14, 2021Other Ministry engagement1:00 PM4:00 PM3.00$62.07FALSE
15December 16, 2022Council Meeting9:00 AM6:00 PM9.00$150.0016.25(my formula fails)
16December 17, 2021Other Ministry engagement1:00 PM4:00 PM3.00$62.07FALSE
17December 20, 2021Council Meeting9:00 AM6:00 PM9.00$150.0016.25(my formula fails)
18Digital Subcommittee0.00$0.00FALSE
19External Ministry Engagement 0.00$0.00FALSE
20MPFAC Meeting Prep0.00$0.00FALSE
21Council Meeting0.00$0.0016.25
220.00$0.00FALSE
230.00$0.00FALSE
T1
Cell Formulas
RangeFormula
C7C7=SUBTOTAL(109,TimeSheet1[Total Hours])
D7D7=TimeSheet1[[#Totals],[Total Hours]]/WorkweekHours
H7H7=EDATE(H4,12)-1
F11:F23F11=IFERROR(IF(COUNT(TimeSheet1[@[Time In]:[Time Out]])=4,(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-#REF!+#REF!-[@[Time In]],IF(AND(LEN([@[Time In]])<>0,LEN([@[Time Out]])<>0),(IF([@[Time Out]]<[@[Time In]],1,0)+[@[Time Out]])-[@[Time In]],0))*24,0)
G11:G23G11=IF([@[Total Hours]]<WorkweekHours,[@[Total Hours]]*20.69,150)
H11:H23H11=IF([@Activity]="Council Meeting", SUM(TimeSheet1[[#Headers],[Total Hours]]:INDEX($F$11:$F67,MATCH([@Activity],$C$11:$C$69,0))))
Named Ranges
NameRefers ToCells
'T1'!WorkweekHours='T1'!$B$7D7, G11:G23
Cells with Data Validation
CellAllowCriteria
B11:B70Any value
C11:C69List=Sheet1!$B$2:$B$8
D11:H70Any value
I11:I14Any value
I15:J71Any value
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,545
Office Version
  1. 365
Platform
  1. Windows
How about
Excel Formula:
=IF([@Activity]="Council Meeting", SUM(F$10:F11)-SUM(H$10:H10),"")
 
Solution

Khatri1001

New Member
Joined
Dec 22, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
How about
Excel Formula:
=IF([@Activity]="Council Meeting", SUM(F$10:F11)-SUM(H$10:H10),"")
Thank you Fluff. Tried it and this works very well.
Appreciate your assistance. :)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,545
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
1,175,610
Messages
5,898,437
Members
434,709
Latest member
Ev1lZer0

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
Top