Best way to count values in a condition

[srosk]

Column AK can have a value of 1 or 0. Individuals listed on my spreadsheet can have be listed on multiple rows. Some rows for AK might be a 1 and others, 0. This data is stored on the sheet 'Report - All Data'.

I have another sheet named discrepancy review. I have a unique list of individuals (removed duplicates). I would like to somehow search against Report - All Data. If anywhere in AK = customer ID and a 1, show message A. Otherwise, message B.

I tried a standard if statement but don't know that is appropriate- I feel like the value in AK row 10 will always overwrite AK3, so would not get consistent results. Thoughts?

 

[Marcelo Branco]

Assuming the Customer IDs are in column A of Report All Data try in the other sheet something like this

	A​	B​
1​	Customer ID​	Messaage​
2​	1​	Message A​
3​	2​	Message B​
4​	3​	Message B​
5​	4​	Message A​
6​	5​	Message B​
7​	6​	Message A​

<tbody>
</tbody>

Formula in B2 copied down
=IF(COUNTIFS('Report - All Data'!A:A,A2,'Report - All Data'!AK:AK,1),"Message A","Message B")

Hope this helps

M.

 

[pharside]

I think you want to use a COUNTIFS, VLOOKUP, and IF. Something like

=if(COUNTIFS(AK:AK, 1, [sheet with column of names], VLOOKUP([CUSTOMER ID CELL], [sheet with column of names], 1, FALSE)>0, "Message A", "Message B")


*edit*
I might have over thought it with the vlookup. =)


=if(COUNTIFS(AK:AK, 1, [sheet with column of names], [CUSTOMER ID CELL])>0, "Message A", "Message B")

 

[srosk]

I've been testing this a few ways with no success. I amon Tenure discrepancy review tab. This tab has the customer unique ID (columnA).

Report – all data, has unique ID in column A, and the value I want tocount in column AK. I even created column AM, which combines value from A and AK into AM. AK contains either a 0 or 1. I tried the following code but it doesn’t work.

=COUNTIF('Report - AllData'!AM:AM,CONCATENATE(A2,"1"))

Thoughts?

 

[Marcelo Branco]

A small data sample along with expected results would be helpful.

M.

 

[srosk]

Not sure the best way to post sample data here. Customer ID (column A) is all numeric. Column AK is either a 0 or a 1. Column AM is either customerID0 or customerID1

Customer ID	Value in AK	Column AK
123456789	0	1234567890
234567891	1	2345678911

<tbody>
</tbody>

There's an example of what's in the Report – all data tab. The discrepancy review tab has customer ID among other data. The discrepancy review tab is doing a countif (a2[customerID]1 [concatenate] is present in column AK of the Report- all data tab.

Hope this helps. Ty for your assistance!

 

[Marcelo Branco]

No need to create AM column.

Try

	A​	B​
1​	Customer ID​	Messaage​
2​	123456789​	Message B​
3​	234567891​	Message A​

Formula in B2 copied down
=IF(COUNTIFS('Report - All Data'!A:A,A2,'Report - All Data'!AK:AK,1),"Message A","Message B")

Check if the sheet name is exactly Report - All Data

M.

 

[srosk]

Thank you Marcelo! I tested your code before and it didn't seem like the counts matched. Apparently, my validations were wrong. Ty again!

No need to create AM column.

Try

	A​	B​
1​	Customer ID​	Messaage​
2​	123456789​	Message B​
3​	234567891​	Message A​

<tbody>
</tbody>

Formula in B2 copied down
=IF(COUNTIFS('Report - All Data'!A:A,A2,'Report - All Data'!AK:AK,1),"Message A","Message B")

Check if the sheet name is exactly Report - All Data

M.

 

[srosk]

Recently got office 2016. Macro now takes 1 min 50 seconds to run on 25k rows. When I add this to the code, balloons to 2 min 20 seconds. Any way to expedite?

 

[Marcelo Branco]

Recently got office 2016. Macro now takes 1 min 50 seconds to run on 25k rows. When I add this to the code, balloons to 2 min 20 seconds. Any way to expedite?

Macro? You hadn't mentioned a macro. I thought you are looking for a formula and i suggested a very simple one.
Hard to help without knowing what you are trying to do and without seeing a data sample.

M.