Mar 24 2022 10:14 PM
I need a formula to show the amount times D and N show in the table between the start and end date.
For example Sam Son would equal 5 and Joe would equal 16
Thank you!
Mar 24 2022 10:55 PM
=COUNTIF(B2:S2, "D")
Hope I was able to help you with this information.
I know I don't know anything (Socrates)
Mar 24 2022 11:01 PM
Mar 24 2022 11:20 PM
Solution@swignall Please see attached file.
Used some named ranges to avoid direct cell references. Two possible solutions.
One with SUMPRODUCT
=SUMPRODUCT(
(
((INDEX(data,MATCH(A6,empl,0),))="D")+((INDEX(data,MATCH(A6,empl,0),))="N")
),
--(dates>=B6),
--(dates<=C6)
)
another with SUM and COUNTIFS
=SUM(
COUNTIFS(
dates, ">="&B6,
dates, "<="&C6,
INDEX(data,MATCH(A6,empl,0),), {"D","N"}
)
)
See which one works best for you.
Mar 24 2022 11:28 PM - edited Mar 24 2022 11:36 PM
Sry was again hasty and didn't read all the way through :)
Here's the formula, untested but should work :)
=SUMPRODUCT((B1:S1>=B6)*(B1:S1<=C6)*(B2:S2="D"))
In hindsight, I recommend Mr. Riny_van_Eekelen formulas, they are more elegant and I think they suit your project better.
Mar 24 2022 11:20 PM
Solution@swignall Please see attached file.
Used some named ranges to avoid direct cell references. Two possible solutions.
One with SUMPRODUCT
=SUMPRODUCT(
(
((INDEX(data,MATCH(A6,empl,0),))="D")+((INDEX(data,MATCH(A6,empl,0),))="N")
),
--(dates>=B6),
--(dates<=C6)
)
another with SUM and COUNTIFS
=SUM(
COUNTIFS(
dates, ">="&B6,
dates, "<="&C6,
INDEX(data,MATCH(A6,empl,0),), {"D","N"}
)
)
See which one works best for you.