SOLVED

Excel Formula for Filtering 2 Columns and Counting the filter cells

%3CLINGO-SUB%20id%3D%22lingo-sub-2963871%22%20slang%3D%22en-US%22%3EExcel%20Formula%20for%20Filtering%202%20Columns%20and%20Counting%20the%20filter%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2963871%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20looking%20to%20create%20a%20formula%20that%20will%20filter%2Fcount%202%20Separate%20columns%20in%20the%20same%20bulk%20data%2C%20and%20the%20counts%20the%20cell%20that%20only%20have%20the%20data%20I'm%20looking%20to%20quantify.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%203000%20Rows%20of%20data%2C%20Column%20B%20has%20%22Email%2C%20Call%2C%20Reminder%2C%20Assigned%20Task%22%3B%20Column%20D%20has%20%22Done%2C%20To%20Do%22.%20I%20want%20a%20formula%20that%20provides%20me%20a%20count%20of%20Column%20B%2C%20that%20matches%20Done%20in%20Column%20D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThoughts%3F%20Help%3F%3C%2FP%3E%3CP%3EI've%20Tried%2C%20not%20successful%3C%2FP%3E%3CP%3E%3DCOUNTA('ACT%20PTD%20YTD'!D%3AD%3D%22Done%22%2C%20'ACT%20PTD%20YTD'!B%3AB%3D%22Call%22)%3C%2FP%3E%3CP%3E%3DCOUNTA('ACT%20PTD%20YTD'!A2%3AF3000(Filter%20'ACT%20PTD%20YTD'!%20D%3AD%2C%22To%20Do%22%2C%20Filter%20'ACT%20PTD%20YTD'!%20B%3AB%2C%22Call%22))%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-2963871%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2963917%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%20for%20Filtering%202%20Columns%20and%20Counting%20the%20filter%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2963917%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1218205%22%20target%3D%22_blank%22%3E%40NathanDellinger%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUMPRODUCT((D2%3AD2954%3D%22Done%22)*(B2%3AB2954%3DF1))%3C%2FP%3E%3CP%3EDynamically%20enter%20serach%20value%20for%20column%20B%20in%20cell%20F1%20with%20above%20formula.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I am looking to create a formula that will filter/count 2 Separate columns in the same bulk data, and the counts the cell that only have the data I'm looking to quantify. 

 

I have 3000 Rows of data, Column B has "Email, Call, Reminder, Assigned Task"; Column D has "Done, To Do". I want a formula that provides me a count of Column B, that matches Done in Column D

 

Thoughts? Help?

I've Tried, not successful

=COUNTA('ACT PTD YTD'!D:D="Done", 'ACT PTD YTD'!B:B="Call")

=COUNTA('ACT PTD YTD'!A2:F3000(Filter 'ACT PTD YTD'! D:D,"To Do", Filter 'ACT PTD YTD'! B:B,"Call"))

 

 

3 Replies
best response confirmed by NathanDellinger (New Contributor)
Solution

@NathanDellinger 

How about

 

=COUNTIFS('ACT PTD YTD'!D:D, "Done", 'ACT PTD YTD'!B:B, "Call")

=COUNTIFS('ACT PTD YTD'!D:D, "To do", 'ACT PTD YTD'!B:B, "Call")

@NathanDellinger 

=SUMPRODUCT((D2:D2954="Done")*(B2:B2954=F1))

Dynamically enter serach value for column B in cell F1 with above formula.

Thanks that work great!, Appreciate the help!