Use a range in an array

[psionman]

I am trying to use a range as the items in an array

Using the Microsoft Office Sales data



In the cell j3 I have the formula

=SUM(SUMIFS($E:$E,$D:$D,"="&J2,$C:$C,{"Jones","Gill"}))

Can I write this so that it picks up the names from the cells j6:j7

e.g.

=SUM(SUMIFS($E:$E,$D:$D,"="&J2,$C:$C,{J6:J7}))

(needless to say this throws an error)

 

[Anonymous1378]

What's stopping you from using:

=SUM(SUMIFS($E:$E,$D:$D,J2,$C:$C,J6:J7))

 

[bebo021999]

Use SUMPRODUCT instead. Do not refer to whole column.
I use 1000 as farest range

=SUMPRODUCT(($D$2:$D$1000=J2)*(COUNTIF($J$6:$J$7,$C$2:$C$1000)>0)*$E$2:$E$1000)

 

[psionman]

What's stopping you from using:

=SUM(SUMIFS($E:$E,$D:$D,J2,$C:$C,J6:J7))

Thanks, but that gives #VALUE! error

 

[psionman]

Use SUMPRODUCT instead. Do not refer to whole column.
I use 1000 as farest range

=SUMPRODUCT(($D$2:$D$1000=J2)*(COUNTIF($J$6:$J$7,$C$2:$C$1000)>0)*$E$2:$E$1000)

Thanks - perfect

 

[Anonymous1378]

Thanks, but that gives #VALUE! error

Perhaps try entering it with Ctrl-Shift-Enter if you're not using Excel 2021 or later?

 

[RoryA]

To that point, I suggest you update your profile information to include the version of office you are using as it can affect what will and won't work as a solution.

 

[psionman]

To that point, I suggest you update your profile information to include the version of office you are using as it can affect what will and won't work as a solution.

I'm sorry I don't use MS products. My workbooks are LibreOffice Calc

 

[RoryA]

Given that this is an Excel forum. that would be useful information to know in your original posts then! Also, this is not the correct area for non-Excel questions, so I will move the thread.