LinkBack URL
About LinkBacks
Hello
I have seen experts on this site use advanced functions/formulas to spill data. I have a table in (A1:D12) and I would like if possible with one formula to output the results (as shown in attached example) G1 and J1.
Thanks
Hello
I have seen experts on this site use advanced functions/formulas to spill data. I have a table in (A1:D12) and I would like if possible with one formula to output the results (as shown in attached example) G1 and J1.
Thanks
Last edited by KenV15; 03-27-2024 at 10:55 PM.
this will give you the names in col G... =UNIQUE(FILTER(A2:A12,(D2:D12="COMPLETE")))
this countifs will give you the numbers in col H... =COUNTIFS($D$2:$D$12,"complete",$A$2:$A$12,G2)
not sure about doing it with one formula. as for what is in J and K, you might have to define what the hours are for days and afternoon.
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
For G1:
=LET(rng,A2:A12,u,UNIQUE(rng),c,COUNTIFS(D2:D12,"Complete",rng,u),SORT(HSTACK(u,c),2,-1))
Clear out K2:K4 and in K2, try:
Please Login or Register to view this content.
An unique formula.
Formula:
Please Login or Register to view this content.
And a new formula for column D
Formula:Please Login or Register to view this content.
WOW! Thank you both for your help
@Gregb11 Is it possible to include J2:J4 in your formula so I can place the formula in J1?
And thank you DJunqueriaYou guys are amazing!
Is it possible to include J2:J4 in your formula so I can place the formula in J1?Please Login or Register to view this content.
@DJunqueira I noticed with your formula in column D if you enter just an employee name with no start or end times the status shows complete. Is there a way to modify your formula to stop this from happening?
I was able to modify your formula with this
Formula:Please Login or Register to view this content.
Last edited by KenV15; 03-27-2024 at 10:48 PM.
One way:if you enter just an employee name with no start or end times the status shows complete. Is there a way to modify your formula to stop this from happening?
=IFS($A2:$A12&$B2:$B12&$C2:$C12="","",($A2:$A12<>"")*(($B2:$B12="")+($C2:$C12="")),"IN PROGRESS",TRUE,"COMPLETE")
Last edited by Gregb11; 03-27-2024 at 10:41 PM.
Thx greg11
Amazing Gregb11
Thank you
You're welcome and thanks for the reps!
This could work also.
Formula:Please Login or Register to view this content.
Tks for the feedback, glad to also help.
Another way,
G2=LET(x,A2:A12,SORT(HSTACK(UNIQUE(x),COUNTIFS(x,UNIQUE(x),D2:D12,"COMPLETE")),2,-1))
J2=LET(x,SUM(ISNUMBER(MATCH(A2:A12,M3:M5,0))*(D2:D12="COMPLETE")),y,COUNTIF(D2:D12,"COMPLETE"),HSTACK(TEXTSPLIT({"Days";"Afternoon";"Total"},,";"),VSTACK(x,y-x,y)))
@ josephteh thx
You are welcome, and thanks for the Rep!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks