Help with formula

[katsdelite]

I'm looking to count jobs by person from a spreadsheet and can't figure out how to combine vlookup with countif - at least that's how I have been approaching it although I'm sure there must be an easier way to do it. Here's sample data, and below is the output I'm looking to produce. Any help to how I should approach this would be greatly appreciated!

Aging Report.xlsm
	B	C	D	E
4	Name	Supervisor	Day	Job
5	John	George	Monday	Parks and Rec
6	John	George	Monday	Lawn Care
7	Tom	Harry	Monday	Parks and Rec
8	Tom	Harry	Monday	Maintanence
9	Jane	Mary	Monday	Parks and Rec
10	Jane	Mary	Monday	Parks and Rec
11	John	George	Tuesday	Lawn Care
12	John	George	Tuesday	Housekeeping
13	Tom	Harry	Tuesday	Lawn Care
14	Tom	Harry	Tuesday	Housekeeping
15	Jane	Mary	Tuesday	Lawn Care
16	Jane	Mary	Tuesday	Parks and Rec
17	John	George	Wednesday	Housekeeping
18	John	George	Wednesday	Maintanence
19	Tom	Harry	Wednesday	Housekeeping
20	Jane	Mary	Wednesday	Housekeeping
21	John	George	Thursday	Maintanence
22	John	George	Thursday	Parks and Rec
23	Tom	Harry	Thursday	Maintanence
24	Jane	Mary	Thursday	Maintanence
25	John	George	Friday	Parks and Rec
26	Tom	Harry	Friday	Housekeeping
27	Jane	Mary	Friday	Lawn Care
Sheet1

Aging Report.xlsm
	G	H	I	J	K
4		Parks and Rec	Lawn Care	Housekeeping	Maintanence
5	John	3	2	2	2
6	Jane
7	Tom
Sheet1

 

[dreid1011]

Perhaps this:

Book1
	B	C	D	E	F	G	H	I	J	K
4	Name	Supervisor	Day	Job			Parks and Rec	Lawn Care	Housekeeping	Maintanence
5	John	George	Monday	Parks and Rec		John	3	2	2	2
6	John	George	Monday	Lawn Care		Jane	3	2	1	1
7	Tom	Harry	Monday	Parks and Rec		Tom	1	1	3	2
8	Tom	Harry	Monday	Maintanence
9	Jane	Mary	Monday	Parks and Rec
10	Jane	Mary	Monday	Parks and Rec
11	John	George	Tuesday	Lawn Care
12	John	George	Tuesday	Housekeeping
13	Tom	Harry	Tuesday	Lawn Care
14	Tom	Harry	Tuesday	Housekeeping
15	Jane	Mary	Tuesday	Lawn Care
16	Jane	Mary	Tuesday	Parks and Rec
17	John	George	Wednesday	Housekeeping
18	John	George	Wednesday	Maintanence
19	Tom	Harry	Wednesday	Housekeeping
20	Jane	Mary	Wednesday	Housekeeping
21	John	George	Thursday	Maintanence
22	John	George	Thursday	Parks and Rec
23	Tom	Harry	Thursday	Maintanence
24	Jane	Mary	Thursday	Maintanence
25	John	George	Friday	Parks and Rec
26	Tom	Harry	Friday	Housekeeping
27	Jane	Mary	Friday	Lawn Care
Sheet2

Cell Formulas
Range	Formula
H5:K7	H5	=COUNTIFS($B$5:$B$27,$G5,$E$5:$E$27,H$4)

 

[katsdelite]

Worked like a charm, thank you! I knew it had to be something simple I'm missing - you just taught me countifs! I tried that too but couldn't wrap my head around it. I appreciate you!

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[dreid1011]

Worked like a charm, thank you! I knew it had to be something simple I'm missing - you just taught me countifs! I tried that too but couldn't wrap my head around it. I appreciate you!

You're welcome, and happy to help.