Hi all, I'm trying to figure this out and would be greatful if someone knows how to do this:
I have to col B with a date and col C with a name - col D and E contain an action taken by col C. I want to reference the date and the name with these two actions and count how many times Name has taken certain actions on which date. Attached a sample sheet to show what I mean.
Cheers
hi Jafs. something like this in J5 maybe?
=COUNTIFS($B$2:$B$37,$I5,$D$2:$D$37,J$4,$C$2:$C$37,"Mike")
not sure how your name varies. if you have just the first name, this would work too:
=COUNTIFS($B$2:$B$37,$I5,$D$2:$D$37,J$4,$C$2:$C$37,LEFT($J$3,FIND(" ",$J$3)-1))
the 2 formulas above can be copied to K5. for L5, change the reference to range Column E instead:
=COUNTIFS($B$2:$B$37,$I5,$E$2:$E$37,L$4,$C$2:$C$37,LEFT($J$3,FIND(" ",$J$3)-1))
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
Put this in J5:
=COUNTIFS($B:$B,$I5,$C:$C,"Mike",$D:$D,J$4)
and copy it to K5:L5. The formula in L5 needs a slight amendment to the range:
=COUNTIFS($B:$B,$I5,$C:$C,"Mike",$E:$E,L$4)
The formula can be copied down if you put other dates in column I.
Hope this helps.
Pete
Hi,
Enter this formula in cell J5 and copy across to the right:
=SUMPRODUCT(($B$2:$B$37=$I5)*($C$2:$C$37=TRIM(SUBSTITUTE($J$3,"stats","")))*($D$2:$E$37=J4))
Regards
Thanks guys, awesome got it to work with your help!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks