Formula Too Long?

[nca77]

hello,

thanks to all who answered my previous question.

here is another:

i am trying to create a shorter formula that will add together the su
of cell B5 * B1.. only if cell B4 = CG3

is there anything i should consider besides using dozens of i
statements?

trying to fit all the cells in my range into the formula gives me
"formula too long"error

here is what i am using and it needs to extend out to column "BI"

=(IF(B$4=CF$3,B5*B$1)+IF(C$4=CF$3,C5*C$1)+IF(D$4=CF$3,D5*D$1)+IF(E$4=CF$3,E5*E$1)+IF(F$4=CF$3,F5*F$1)+IF(G$4=CF$3,G5*G$1)+IF(H$4=CF$3,H5*H$1)+IF(I$4=CF$3,I5*I$1)+IF(J$4=CF$3,J5*J$1)+IF(K$4=CF$3,K5*K$1)+IF(L$4=CF$3,L5*L$1)+IF(M$4=CF$3,M5*M$1)+IF(N$4=CF$3,N5*N$1)+IF(O$4=CF$3,O5*O$1)+IF(P$4=CF$3,P5*P$1)+IF(Q$4=CF$3,Q5*Q$1)+IF(R$4=CF$3,R5*R$1)+IF(S$4=CF$3,S5*S$1)+IF(T$4=CF$3,T5*T$1)+IF(U$4=CF$3,U5*U$1)+IF(V$4=CF$3,V5*V$1)+IF(W$4=CF$3,W5*W$1)+IF(X$4=CF$3,X5*X$1)+IF(Y$4=CF$3,Y5*Y$1)+IF(Z$4=CF$3,Z5*Z$1))/SUMIF($B$4:$BI$4,$CF$3,$B$1:$BI$1)

Thanks in Advance!

Nic

 

[Ken Wright]

Try this:-

=SUMPRODUCT((($B$4:$BI$4)=$CF$3)*($B$5:$BI$5*$B$1:$BI$1))/SUMIF($B$4:$BI$4,$CF$3
,$B$1:$BI$1)

 

[nca77]

THANK