COUNTIF/SUM(IF/SUMIF/COUNTIFS?

%3CLINGO-SUB%20id%3D%22lingo-sub-2376438%22%20slang%3D%22en-US%22%3ECOUNTIF%2FSUM(IF%2FSUMIF%2FCOUNTIFS%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2376438%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20tried%20several%20different%20formulas%20and%20I%20just%20cannot%20figure%20out%20what%20I%20need%20to%20use.%20I%20have%20been%20googling%20for%20hours%20and%20hope%20someone%20can%20help.%20I%20am%20trying%20to%20monitor%20work%20being%20completed%20and%20have%20managed%20to%20figure%20out%20formulas%20for%20the%20right%20hand%20side%20table%20for%20all%20except%20the%20last%20column.%20I%20want%20to%20be%20able%20to%20count%20how%20many%20projects%20each%20person%20has%20worked%20on%20are%20completed%20so%20I%20need%20it%20to%20say%20that%20if%20their%20name%20appears%20in%20column%20A%20or%20B%20and%20that%20row%20has%20a%20date%20in%20Column%20C%20then%20that%20counts%20as%201.%20Anyone%20know%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20made%20dummy%20version%20of%20my%20main%20spreadsheet%20to%20attach%20so%20I%20could%20create%20false%20names.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2376438%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2376484%22%20slang%3D%22en-US%22%3ERe%3A%20COUNTIF%2FSUM(IF%2FSUMIF%2FCOUNTIFS%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2376484%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1060204%22%20target%3D%22_blank%22%3E%40Kelzysned%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20H2%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DCOUNTIFS(%24A%242%3A%24A%249%2CE2%2C%24C%242%3A%24C%249%2C%22%26lt%3B%26gt%3B%22)%2BCOUNTIFS(%24B%242%3A%24B%249%2CE2%2C%24C%242%3A%24C%249%2C%22%26lt%3B%26gt%3B%22)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eor%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DSUMPRODUCT((%24A%242%3A%24B%249%3DE2)*(%24C%242%3A%24C%249%26lt%3B%26gt%3B%22%22))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFill%20down%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I have tried several different formulas and I just cannot figure out what I need to use. I have been googling for hours and hope someone can help. I am trying to monitor work being completed and have managed to figure out formulas for the right hand side table for all except the last column. I want to be able to count how many projects each person has worked on are completed so I need it to say that if their name appears in column A or B and that row has a date in Column C then that counts as 1. Anyone know? 

 

I have made dummy version of my main spreadsheet to attach so I could create false names. 

 

 

1 Reply

@Kelzysned 

In H2:

 

=COUNTIFS($A$2:$A$9,E2,$C$2:$C$9,"<>")+COUNTIFS($B$2:$B$9,E2,$C$2:$C$9,"<>")

 

or

 

=SUMPRODUCT(($A$2:$B$9=E2)*($C$2:$C$9<>""))

 

Fill down