Counting 2 cells that meet 2 specific criteria

Senior Member

Hey all, extremely new to this and looking for some insight. 

I'm working on a spreadsheet to tally specific tasks done by specific people, but I have a feeling it's going to end up being a pretty complex formula beyond my ability. 

Column A 2-45 are used to input the persons name, and L 2-45 to monitor actions taken.
The spread sheet has 4 people to select from 5 actions taken. 
So Ideally, I'd like to create a If A2=Person AND L2=action, it would mark this in a separate table I've to count how many of each action each individual has done. 

I've tried a formula from what little knowledge I have that included counting both A2:A45 for person and L2:L45 for action (=COUNTIF(B2:B46,"person")*COUNTIF(L2:L46,"action")), but obviously this was counting instances of both for the whole range. 

Any recommendations on how best to approach this? From my understanding I'd imagine it'd have to be a pretty complex Countif &=sum A2person+L2 action, A3person+L3action,A4person etc formula, for each person and action, but not sure if there was an easier way. 

1 Reply

@Afranks335 Sounds like you could use a pivot table for this, providing that the two columns (A and L) are part of a larger data set, without empty columns between them. Alternatively, you can use COUNTIFS. Attached a file containing both.