How to recognize text strings in INDEX-MATCH

[expertlypaul]

I have multiple spreadsheets of marketing email data where I want to identify how many times a single user has clicked a link. Each spreadsheet represents a unique email that was sent out with a clicked column. The column has one of two results, TRUE or FALSE. Here is the formula I'm using in totality.

=SUM(IF(COUNTIF('Email 1'!$A$2:$A$22,A2)>0,IF(INDEX('Email 1'!$A$2:$S$22,MATCH(A2,'Email 1'!$A$2:$A$22,0),18)="TRUE",1,0),0))

I have tested the INDEX MATCH to see if it's pulling the right value and all is well. If the referenced email exists, then it finds the TRUE or FALSE cell under the clicked column.

When I include the IF wrapper, it doesn't seem to be able to recognize the "TRUE" text string, so marks everything as false/gives it a value of zero.

I've attempted changing the clicked column formatting to "plain text," but that didn't change anything.

Any thoughts on what the issue might be?

 

[dfsobral]

really don´t know if I understood

column 18 is "R" where the TRUE/FALSO answer are, right?

you want to count how many cells in column R have answer TRUE considering that in range A2:A22 the answer is equal to A2

is this what you want

=COUNTIFS('Email 1'!$A$2:$A$22,A2,'Email 1'!$R$2:$R$22)

 

[expertlypaul]

Thanks for asking the clarifying questions. I would like to take a list of email addresses in Sheet 1, and for each email address, count how many times column R is "TRUE." There are four sheets this value needs to be found/counted:

Sheet 1: Source of Email Addresses, Column C is where the SUM formula would sit.
Sheets 2-5: Is Column R TRUE or FALSE (if TRUE=1, ELSE=0)

 

[dfsobral]

Thanks for asking the clarifying questions. I would like to take a list of email addresses in Sheet 1, and for each email address, count how many times column R is "TRUE." There are four sheets this value needs to be found/counted:

Sheet 1: Source of Email Addresses, Column C is where the SUM formula would sit.
Sheets 2-5: Is Column R TRUE or FALSE (if TRUE=1, ELSE=0)

so on sheet1 you'll have a list or e-mail in column A and in column C you want to know how many times that mail shows on the other 4 sheets

on the other sheets the e-mail appears in column A (i suppose for your example)

the column R don't seams to be needed

try like this

=COUNTIF('Email 1'!$A$2:$A$22,A2) + COUNTIF('Email 2'!$A$2:$A$22,A2) + COUNTIF('Email 3'!$A$2:$A$22,A2) + COUNTIF('Email 4'!$A$2:$A$22,A2)

 

[expertlypaul]

so on sheet1 you'll have a list or e-mail in column A and in column C you want to know how many times that mail shows on the other 4 sheets

on the other sheets the e-mail appears in column A (i suppose for your example)

the column R don't seams to be needed

try like this

=COUNTIF('Email 1'!$A$2:$A$22,A2) + COUNTIF('Email 2'!$A$2:$A$22,A2) + COUNTIF('Email 3'!$A$2:$A$22,A2) + COUNTIF('Email 4'!$A$2:$A$22,A2)

On the other sheets, there is a column (A) of email addresses and a column (R) for whether or not that email address CLICKED.

I want to SUM across sheets 2-5 how many times that individual email address clicked.

 

[dfsobral]

ok

try this, had the same for the other sheet

=SUMPRODUCT(--('Email 1'!$A$2:$A$22=A2), --('Email 1'!$R$2:$R$22=TRUE))