Counting 2 cells that meet 2 specific criteria

%3CLINGO-SUB%20id%3D%22lingo-sub-2406549%22%20slang%3D%22en-US%22%3ECounting%202%20cells%20that%20meet%202%20specific%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2406549%22%20slang%3D%22en-US%22%3E%3CP%3EHey%20all%2C%20extremely%20new%20to%20this%20and%20looking%20for%20some%20insight.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EI'm%20working%20on%20a%20spreadsheet%20to%20tally%20specific%20tasks%20done%20by%20specific%20people%2C%20but%20I%20have%20a%20feeling%20it's%20going%20to%20end%20up%20being%20a%20pretty%20complex%20formula%20beyond%20my%20ability.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EColumn%20A%202-45%20are%20used%20to%20input%20the%20persons%20name%2C%20and%20L%202-45%20to%20monitor%20actions%20taken.%3CBR%20%2F%3EThe%20spread%20sheet%20has%204%20people%20to%20select%20from%205%20actions%20taken.%26nbsp%3B%3CBR%20%2F%3ESo%20Ideally%2C%20I'd%20like%20to%20create%20a%20If%20A2%3DPerson%20AND%20L2%3Daction%2C%20it%20would%20mark%20this%20in%20a%20separate%20table%20I've%20to%20count%20how%20many%20of%20each%20action%20each%20individual%20has%20done.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EI've%20tried%20a%20formula%20from%20what%20little%20knowledge%20I%20have%20that%20included%20counting%20both%20A2%3AA45%20for%20person%20and%20L2%3AL45%20for%20action%20(%3DCOUNTIF(B2%3AB46%2C%22person%22)*COUNTIF(L2%3AL46%2C%22action%22))%2C%20but%20obviously%20this%20was%20counting%20instances%20of%20both%20for%20the%20whole%20range.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EAny%20recommendations%20on%20how%20best%20to%20approach%20this%3F%20From%20my%20understanding%20I'd%20imagine%20it'd%20have%20to%20be%20a%20pretty%20complex%20Countif%20%26amp%3B%3Dsum%20A2person%2BL2%20action%2C%20A3person%2BL3action%2CA4person%20etc%20formula%2C%20for%20each%20person%20and%20action%2C%20but%20not%20sure%20if%20there%20was%20an%20easier%20way.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2406549%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20Scripts%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2406724%22%20slang%3D%22en-US%22%3ERe%3A%20Counting%202%20cells%20that%20meet%202%20specific%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2406724%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1068860%22%20target%3D%22_blank%22%3E%40Afranks335%3C%2FA%3E%26nbsp%3BSounds%20like%20you%20could%20use%20a%20pivot%20table%20for%20this%2C%20providing%20that%20the%20two%20columns%20(A%20and%20L)%20are%20part%20of%20a%20larger%20data%20set%2C%20without%20empty%20columns%20between%20them.%26nbsp%3BAlternatively%2C%20you%20can%20use%20COUNTIFS.%20Attached%20a%20file%20containing%20both.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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.