SUMPRODUCT issue/support

[MARKA1966]

Anyone help with this small issue.

I am trying to sum a range of three cells (B2 - D16) but where the Region (A2:A16) does not equal Scotland and Wales.

Appears straight forward but really frustrating :) 

[#2]

@MARKA1966 Use this:

=SUMPRODUCT((B2:B16),--(A2:A16<>"Scotland"),--(A2:A16<>"Wales"))

or

=SUMPRODUCT(B2:B16*(A2:A16<>"Scotland")*(A2:A16<>"Wales"))

Or perhaps you need to use E2:E16 as the first range, if you want to sum the Total column.

[#3]

@MARKA1966 

It is straightforward given the correct version of Excel

= SUM(
      FILTER(data,
         (country<>"Scotland")
           *(country<>"Wales")
      )
   )

[#4]

@MARKA1966 

While I think of it

= SUMPRODUCT(data
      *(Region<>"Wales")
      *(Region<>"Scotland")
   )

also works.  The sole purpose of SUMPRODUCT rather than SUM is to get around the implicit intersection step that is used to cripple legacy Excel.

[#5]

Hi @MARKA1966 

Your column E already SUMs columns B-D. Alternative:

=SUMIFS(E2:E16,
    A2:A16, "<>wales",
    A2:A16, "<>scotland"
)