Two related columns count exception formula

[BowerAdamt]

I have two columns that are related.

Essentially I have a column of keywords - some of these keywords are good leads some are bad.


Screenshot.png

in column D i have tagged each lead Y or N for yes or no - I want to be able to count how many of each keyword is a Yes or No.

I hope that makes sense.

Regards

A

[AlKey]

For Y

=COUNTIF(D:D,"Y")

For N

=COUNTIF(D:D,"N")

[BowerAdamt]

Thanks for the reply. I need to include the other column with the keywords in this, so it needs to have two exceptions I imagine it being something like:

=CountIf (A:A, "Keyword A") & =CountIf (D:D "Y")

So if i have a column of entries:

A1 is 'Keyword A'
A2 is 'Keyword A'
A3 is 'Keyword B'

I want to check Column D to see if it has a Y or an N

D1 is 'Y'
D2 is 'N'
D3 is 'Y'

And from this i can generate totals like:

Keyword: Total yes Total No
Keyword A - 23 10
Keyword B - 11 9

Etc.

hope that helps, sorry if im not to clear im new to this. I really appreciate your help.

[BowerAdamt]

So essentially i want to detect which keyword is in column A and then detect if it is a Yes or a No

I have attached a screenshot:

screenshot 2.png

Then I would ideally be able to figure out how many Yes or No's of each keyword I would have.

thanks again

[solnajeff]

Hi

You can use the SUMPRODUCT function to achieve the result you are looking for.

Attached is a sample file

regards

Jeff

[FDibbins]

Hi and welcome to the forum

For future reference, please do not upload a picture of your file...rather, upload a sample of your workbook, showing what data you are working with, a few samples of your expected outcome is (manually entered is ok) and how you arrived at that. (exclude sensitive info). Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you Also, not all members can upload picture files (Company firewalls and stuff)

You can do this with countifS()

	A	B	C	D	E	F	G
1	K			Lead		Y	N
2	K a			y	K a	3	0
3	K b			n	K b	1	1
4	K a			y	K c	0	1
5	K b			y	K d	1	1
6	K c			n
7	K a			y
8	K d			n
9	K d			y

F2=COUNTIFS($A$2:$A$9,$E2,$D$2:$D$9,F$1) copied down and across

[BowerAdamt]

Thanks this has been a great help! i will mark this as solved!