SumProduct and excluding specific cells

G

Gunjani

How do I exclude certain cells in a range from a calculation

e.g =SUMPRODUCT(($A$26:$A$147=$A151)*($B$26:$BE$147=B$150))
but I wish to exclude columns
A26:B42,I26:J42,W26:X42,A47:B63,I47:J63,W:26:X42,A68:B84 etc
 
H

Harlan Grove

How do I exclude certain cells in a range from a calculation

e.g =SUMPRODUCT(($A$26:$A$147=$A151)*($B$26:$BE$147=B$150))
but I wish to exclude columns
A26:B42,I26:J42,W26:X42,A47:B63,I47:J63,W:26:X42,A68:B84 etc
Click to expand...

Simplest would be to add another criteria range, maybe in column BZ. Enter 'x'
in col BZ to exclude that row, e.g., entering 'x' in BZ35 would exclude row 35
in the other ranges. Then change your formula to

=SUMPRODUCT(($BZ$26:$BZ$147<>"x")*($A$26:$A$147=$A151)*($B$26:$BE$147=B$150))
 
Top