COUNTIF in a 2D Array and multiply by value in the same row

[Penguinton]

Hello all, long time reader, first time questioner.

I have a spreadsheet with a set of movements through various sections (B:O) in the Routing tab, and the number of times they move in a given year (P:T). I need to return the amount of times they move through each section in each year in the Total by Section tab - a COUNTIF returns the number of times each section comes up, but for each I need to multiply it by the number of movements on the row in the Routing tab and I have been unable to figure out how to do this.

For example, take ID3 in the Routing tab - it comes up 7 times in each year, so needs to increment the count against each of the relevant sections 7 times in the Total by Section tab.

Thanks very much for your help.

[FDibbins]

Would this work for you?

=$F6*INDEX(Routing!P:P,MATCH('Total by section '!$A6,Routing!$A:$A,0))

[Penguinton]

Thanks for the answer! I gave it a try - I'm afraid not as the ID figures in the Routing tab do not correspond to the IDs in Total by Section - the former is the ID of the service, the latter is the ID of the section name.

[Penguinton]

A colleague provided this, which certainly works but is quite unwieldy:

=SUMIF(Routing!$B$3:$B$134,'Total by section '!$E5,Routing!P$3:P$134)+SUMIF(Routing!$C$3:$C$134,'Total by section '!$E5,Routing!P$3:P$134)+SUMIF(Routing!$D$3:$D$134,'Total by section '!$E5,Routing!P$3:P$134)+SUMIF(Routing!$E$3:$E$134,'Total by section '!$E5,Routing!$P4:$P135)+SUMIF(Routing!$F$3:$F$134,'Total by section '!$E5,Routing!$P4:$P135)+SUMIF(Routing!$G$3:$G$134,'Total by section '!$E5,Routing!$P4:$P135)+SUMIF(Routing!$H$3:$H$134,'Total by section '!$E5,Routing!$P4:$P135)+SUMIF(Routing!$I$3:$I$134,'Total by section '!$E5,Routing!$P4:$P135)+SUMIF(Routing!$J$3:$J$134,'Total by section '!$E5,Routing!$P4:$P135)+SUMIF(Routing!$K$3:$K$134,'Total by section '!$E5,Routing!$P4:$P135)+SUMIF(Routing!$L$3:$L$134,'Total by section '!$E5,Routing!$P4:$P135)+SUMIF(Routing!$M$3:$M$134,'Total by section '!$E5,Routing!$P4:$P135)+SUMIF(Routing!$N$3:$N$134,'Total by section '!$E5,Routing!$P4:$P135)+SUMIF(Routing!$O$3:$O$134,'Total by section '!$E5,Routing!$P4:$P135)

As there are up to 24 sections to consider in the actual spreadsheet, I'd be very grateful if someone could provide a more elegant solution.

[XLent]

Hi, you can use below:

Please Login or Register  to view this content.

note: your SUMIF approach above will produce different results as the absolute referencing for sections E:O are incorrect, should be P$3:P$134 as opposed to $P3:$P134
once corrected the SUMIF approach will generate the same results as the above

[kvsrinivasamurthy]

IN G4 and copied across

=SUMPRODUCT(COUNTIF(OFFSET(Routing!$B$3:$O$3,ROW(Routing!$A$3:$A$105)-ROW(Routing!$A$3),0),$E4)*(Routing!P$3:P$105))

[XLent]

can you explain why you would choose to offer an inefficient and volatile SUMPRODUCT construct when it has already been shown to be unnecessary

what am I missing ? are you taking the thread title as a literal requirement ?

[Penguinton]

Hi, you can use below:

Please Login or Register  to view this content.

note: your SUMIF approach above will produce different results as the absolute referencing for sections E:O are incorrect, should be P$3:P$134 as opposed to $P3:$P134
once corrected the SUMIF approach will generate the same results as the above

This solution works perfectly, thank you very much for your help.