Mar 30 2022 01:31 AM
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 :)
Mar 30 2022 01:43 AM - edited Mar 30 2022 01:48 AM
@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.
Mar 30 2022 01:46 AM
It is straightforward given the correct version of Excel
= SUM(
FILTER(data,
(country<>"Scotland")
*(country<>"Wales")
)
)
Mar 30 2022 02:14 AM
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.
Mar 30 2022 03:23 AM
Hi @MARKA1966
Your column E already SUMs columns B-D. Alternative:
=SUMIFS(E2:E16,
A2:A16, "<>wales",
A2:A16, "<>scotland"
)