countif to become an array

[charlesloves2read]

Hi folks, I am using a really long formula of countif and I am looking for a way to simplify them.

Basically I am copying and pasting it from row 4 to row 91! Thanks!

=COUNTIF(E$4:Q$4,S4)*D$4

+COUNTIF(E$5:Q$5,S4)*D$5

+COUNTIF(E$6:Q$6,S4)*D$6

+.....

+COUNTIF(E$91:Q$91,S4)*D$91

 

[Fluff]

What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also can you post some sample data, along with expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Fluff]

Thanks for updating your profile.
In the absence of any data, this is a guess

=SUM(MMULT(TRANSPOSE($D$4:$D$91),--($E$4:$Q$91=S4)))

 

[Fluff]

Another option

=SUMPRODUCT(($E$4:$Q$91=S4)*($D$4:$D$91))

 

[charlesloves2read]

Another option

=SUMPRODUCT(($E$4:$Q$91=S4)*($D$4:$D$91))

this worked like a charm and was exactly what i was looking for. thanks so much Fluff!

 

[Fluff]

You're welcome & thanks for the feedback.