sumproduct (Excel 2000)

Topic

New Reply

Can anybody help me out with my formulae in sheet ‘recap-1’ and comes out with total amount for each group of Months?

-indra-

Reply | Quote

Replies

In D3:

=SUMPRODUCT((IDSB34!$A$3:$A$355=$B3)*(IDSB34!$E$3:$E$355=D$2),IDSB34!$H$3:$H$355+IDSB34!$I$3:$I$355+IDSB34!$J$3:$J$355+IDSB34!$K$3:$K$355)

and in F3:

=SUMPRODUCT((IDSB34!$A$3:$A$355=$B3)*(IDSB34!$E$3:$E$355=F$2),IDSB34!$L$3:$L$355+IDSB34!$M$3:$M$355+IDSB34!$N$3:$N$355+IDSB34!$O$3:$O$355+IDSB34!$P$3:$P$355+IDSB34!$Q$3:$Q$355+IDSB34!$R$3:$R$355+IDSB34!$S$3:$S$355)

This can be copied to the other cells.

Reply | Quote

Many thanks, Hans.

cheers,

Reply | Quote

thanks for * Rory.

-indra-

Reply | Quote

Hi,
=SUMPRODUCT((IDSB34!$A$3:$A$355=$B3)*(IDSB34!$E$3:$E$355=D$2)*IDSB34!$H$3:$K$355) in D3, and
=SUMPRODUCT((IDSB34!$A$3:$A$355=$B3)*(IDSB34!$E$3:$E$355=F$2)*IDSB34!$L$3:$S$355) in F3.
Copy down/across as needed.
HTH

Reply | Quote