Using a Numeric Value Cell Reference Within A Multi-condition (explained below) SUMIFS Argument

[hitch35]

Hello all

I'm running into an issue where I'd like to use a cell reference as input into a sumifs function. The cell reference will contain an numeric entry, and will have between 5 to 10 entries (in separate cell references).

Let’s say the cell references I’d like to have in the formula are:

A1 (a numeric 858187 as in the formula below)
A2 (a numeric 858191 as in the formula below)
A3 (a numeric 847304 as in the formula below)
etc...

This is what I have so far, which works great looking for the two conditioned inputs, but I'm hoping to change the numerical values at the end of the code {858187;858191;847304} with cell references, so something I imagine would look like {"="&A1;"="&A2;"="&A3}, but it doesn't seem to work out so nicely (as is usually the story.)

Here is a copy of what I currently have (again, works great, just looking to substitute the manually entered numeric values with cell references A1, A2 and A3)

=SUM(SUMIFS('Worksheet1'!K:K,Worksheet1’!D:D,{"CNX","OB","IB"},'Worksheet1'!A:A,'Worksheet2'!B96,Worksheet1'!G:G,{858187;858191;847304}))

The area I’d like to apply cell references is ---> {858187;858191;847304}

*NOTE* For anybody willing to provide some 'outside the box' ideas, 1) Thank you, 2) I'm trying to stay away from VB, and to be honest, I'm not a huge fan of having 10 sumifs arguments in the same cell (as it needs to be scaled out to over a thousand cells within the workbook, and the size of the file needs to be kept small, and 3) ok... I guess if you've got some VB that will do the trick (and is relatively easy to reproduce), suggest away!

Thank you so much for your help. Please be patient, as it’s easy for me to think I’ve provided enough context, when that may not be the case.

Sincerely,

First(ish) time poster, and long time lover of Mr. Excel

 

[hitch35]

Someone gave me a solution to this problem, so I thought I would post............

lets assume the 3 numbers are in cells Q1:Q3 - keep these criteria together. This would return the desired results:

=SUMPRODUCT(SUMIFS(Worksheet1!K:K,Worksheet1!D:D,{"CNX","OB","IB"},Worksheet1!A:A,Worksheet2!B96,Worksheet1!G:G,Worksheet1!Q1:Q3))

you can also use the Array entered equivalent:

=SUM(SUMIFS(Worksheet1!K:K,Worksheet1!D:D,{"CNX","OB","IB"},Worksheet1!A:A,Worksheet2!B96,Worksheet1!G:G,Worksheet1!Q1:Q3))

By Array entered I mean you must press Ctrl Shift Enter to enter the formula not Enter. Its shorter but slower to run than the SUMPRODUCT version.