Counting Customers Based on One Table & Inputs

Hello,

I am having a hard time figuring out how to derive the correct value based on multiple inputs and two data sources.

There is Data Sheet 1 which contains hours that an event occurs for particular resources, Data Sheet 2 which has all the relevant data associated with a resource, and lastly the inputs to ascertain the applicable customers.

I am attempting to determine if a resource has an event hour greater than 0 then it will find all the resources on Data Sheet 2, if the count type has an input of yes then it will find all the count types associated with "count of enrolled", and if the date of 4/11/2021 for example then it will select the month of April and all of these inputs will then be summed in one cell.

Here is the formula that I had written, but it was not keeping the solution into one cell and it was spilling down. It was also giving me a number of customers when it should have been zero.

I'm totally stumped, so any help would be greatly appreciated.

if YES, means, "Count of enrolled", then your formula has it backwards - it says if it's "yes", the look for "Count of program only"

=XLOOKUP(B4:B6&(TEXT(C25,"mmmm"))&(IF(C24="yes","count of program Only","Count of Enrolled")),F4:F21&H4:H21&G4:G21,I4:I21)

Anyway, try this in C28:

=SUM(XLOOKUP(FILTER($B$4:$B$6,$C$4:$C$6<>0)&IF($C$24="yes","Count of Enrolled","count of program Only")&TEXT($C$25,"mmmm"),$F$4:$F$21&$G$4:$G$21&$H$4:$H$21,$I$4:$I$21,,0))

Thank you! This is exactly what I was looking for you are a godsend! I will have to study up on that filter formula, I am not as familiar with it.

You're welcome, and thanks for the rep.