countinfs with 2 criteria, need to add non duplicated count

[Mark_Delaware]

Hey All,

I am comparing two columns by row looking for blanks in column C5:C182, and a "y" in the second column G5:G182. Which has worked thus far, th problem I am having is that I need non duplicated count of "y" in column G5:G182 based on names in a third column, B5:B182.


this is what I am using at teh monment, =COUNTIFS(C5:C182,"",G5:G182,"y")

Thoughts?

[MarvinP]

Hi Mark,

We really need a sample workbook to see to solve this. You can attach a sample by clicking on "Go Advanced" and then the Paper Clip Icon above the advanced message box.

Will you allow a "Helper Column" to be added to the data?

[Mark_Delaware]

I have to change names listed in the sheet and I will shortern it before I attach it andyes go ahead and modify as needed. I will send a working copy and not the actual file.

[Mark_Delaware]

Basically the answer I need from the sample sheet is 3, but my formula shows 4. One name is counted twice, I want a count of "y" based on column C not being blank (I changed my question to match the sample sheet attached)and non duplicated names from column B

I hope this isn't clear as mud

[MarvinP]

Hi Mark,

See if this "Helper Column" answer will work for you.

[sktneer]

Try this Array Formula which requires confirmation with Ctrl+Shift+Enter instead of Enter alone.

Please Login or Register  to view this content.

Is this what you are trying to achieve?

[MarvinP]

Hi Mark,

Here is another way to get an answer using SumProduct in a Helper Column.