Excel Formula improvement

[ItalianPlatinum]

Hello - In a long round about way the below formula fits its purpose. But i wanted to know for those excel experts is there a cleaner more efficient way to accomplish the below? Or a better way you would have chosen?

long story short Sheet2 has all the data (600,000+ rows). Sheet1 is merely a summary.

=COUNTIFS(Sheet1!$S:$S,Sheet2!A4,Sheet1!$U:$U,Sheet2!$B$1)+COUNTIFS(Sheet1!$W:$W,Sheet2!A4,Sheet1!$Y:$Y,Sheet2!$B$1)+COUNTIFS(Sheet1!$AA:$AA,Sheet2!A4,Sheet1!$AC:$AC,Sheet2!$B$1)+COUNTIFS(Sheet1!$AE:$AE,Sheet2!A4,Sheet1!$AG:$AG,Sheet2!$B$1)+COUNTIFS(Sheet1!$AI:$AI,Sheet2!A4,Sheet1!$AK:$AK,Sheet2!$B$1)

User	C	DE	A
AA	Above Formula	Above Formula	Above Formula
BB	Above Formula	Above Formula	Above Formula
CC	Above Formula	Above Formula	Above Formula

 

[felixstraube]

Can you show us you data in Sheet2 and explain what you want to accomplish?

 

[ItalianPlatinum]

Can you show us you data in Sheet2 and explain what you want to accomplish?

its a lot of data. its basically taking the first countif S:S will have the user (AA) then U:U will have (either C, DE, or A). so trying to count how many times a user does C, DE, or A. Then their is 5 iteration of that. Continuing on the next countifs. same concept. Not sure if that helps or hurts

 

[Fluff]

You could try

=COUNTIFS(Sheet1!$S:$AI,$A4,Sheet1!$U:$AK,B$1)

 

[ItalianPlatinum]

@Fluff oh i like that one!

 

[Fluff]

Glad we could help & thanks for the feedback.