Counting Data w/Look-up - without using an Array

[bdav1216]

Hello,

See the attached spreadsheet. I need help applying the logic below to the spreadsheet. The formula's themselves, I suspect, won't be that difficult, but need to be applied to the lookup tab and I need to avoid using array's as my actual data will contain many rows/columns. The logic below is very similar for these so hopefully that will be easier.

Thank you for your help!!!!!!

If the Value in Column AK is equal to value in the Look-Up Tab (Column A), Count the names and output the value in Column D. The name of the associate (Column A) should match to the Lookup File (Column D) and output in the correct value for each associate.

If the Value in Column AV is equal to value in the Look-Up Tab (Column B), Count the names and output the value in Column E. The name of the associate (Column A) should match to the Lookup File (Column D) and output in the correct value for each associate.

If the Value in Column AW is equal to value in the Look-Up Tab (Column C), Count the names and output the value in Column F. The name of the associate (Column A) should match to the Lookup File (Column D) and output in the correct value for each associate.

If Column AK AND Column AV have the same value, count the names and output the value in Column C. The name of the associate (Column A) should match to the Lookup File (Column D) and output in the correct value for each associate.

IF Column CJ = "Complex" AND Column L = "New", count the number in Column I for each unique associate in the lookup. The name of the associate (Column A) should match to the Lookup File (Column D) and output in the correct value for each associate.

[JohnTopley]

Please add some manual results so we can check the results of any formulae: cover all conditions specified in your post.

This probably requires adding more data as there usually just one entry per associate on your file.

[JohnTopley]

Questions:

Can the entries in Column B of "Lookup" appear in column AK? And similarly for AV and AW?

IF Column CJ = "Complex" AND Column L = "New", count the number in Column I

I is a date so you simply want a count of non-blanks for a given associate?

[bdav1216]

I added the manual expected values.

One update for the last formula.

IF Column CJ = "Complex" AND Column L = "New", count the number in Column I for each unique associate in the lookup (Column AV matching Column B in the lookup). The name of the associate (Column A) should match to the Lookup File (Column D) and output in the correct value for each associate.

[bdav1216]

Questions:

Can the entries in Column B of "Lookup" appear in column AK? And similarly for AV and AW?

Yes, the entries can appear in AK, AV or AW.

IF Column CJ = "Complex" AND Column L = "New", count the number in Column I
I is a date so you simply want a count of non-blanks for a given associate?

Column I should not be a date (It should output a number, and I have made that update to the format of the column). I would like the aggregate count for an associcate in that column

[JohnTopley]

Column defined should be C,D,E and B not as your description!

[JohnTopley]

I created 4 named ranges based on "Lookup":

CSC, PRIMARY, SECONDARY, UNIQUE

All formulae are of this form:

in B2

=IFERROR(IF(MATCH($A2,UNIQUE,0),COUNTIFS('Raw Data'!$AK$2:$AK$100,$A2,'Raw Data'!$AV$2:$AV$100,$A2),0),0)

Check if Associate is in the appropriate range (CSC etc): if YES, then do the COUNTIF.

in C2

=IFERROR(IF(MATCH($A2,CSC,0),COUNTIF('Raw Data'!$AK$2:$AK$100,$A2),0),0)

in D2

=IFERROR(IF(MATCH($A2,PRIMARY,0),COUNTIF('Raw Data'!$AV$2:$AV$100,$A2),0),0)

in E2

=IFERROR(IF(MATCH($A2,SECONDARY,0),COUNTIF('Raw Data'!$AW$2:$AW$100,$A2),0),0)

in H2

=IFERROR(IF(MATCH($A2,UNIQUE,0),COUNTIFS('Raw Data'!$CJ$2:$CJ$100,"Complex",'Raw Data'!$L$2:$L$100,"New",'Raw Data'!$I$2:$I$100,$A2),0),0)