column of values for criteria in a sumif formula

0

I'm familiar with the use of sumif and sumifs.  However, is there a way that I can use a column of numbers as criteria instead of specifying a certain cell or number?  The column of numbers will vary based on preceeding operations but the rest of the formula will be constant.  The column for criteria will vary from only one to perhaps 10 numbers.

I've added a sample very simple spreadsheet that will describe what I want to do.

Answer
Discuss

Discussion

I'm pretty sure that what you have in mind is possible, if need be with an array formula. I might come up with a solution if you could post a workbook with a precise illustration of what you want to do.
Variatus (rep: 2613) Oct 16, '18 at 11:06 pm
I'm not exactly sure what you're asking, but my initial thought is to use INDIRECT() to reference the changing cells - maybe I'm reading it wrong though.
don (rep: 1655) Oct 17, '18 at 2:27 am
I added a simple spreadsheet to show what I'm wanting to accomplish.
pduffer Oct 17, '18 at 9:27 am
Add to Discussion

Answers

0

The formula below will do what you want.

[CODE=SUMPRODUCT(SUMIF(Data1,Codes,Amounts))+SUMPRODUCT(SUMIF(Data2,Codes,Amounts))[/CODE]

To make it easier to read I named the ranges as follows.

  • Data1 = L5:L21 in your worksheet
  • Data2 = M5:M21 in your worksheet
  • Amounts = N5:N21 in your worksheet
  • Codes = A5:A7 in your sample worksheet

You can replace the names in the formula with the actual range addresses but since at least the Codes range is dynamic it will be a lot more efficient to apply the dynamism to the named range specification than to every formula. Look up "Dynamic named ranges" or ask on this forum.

The funny thing is that my method can handle only one column. There must be a better way to this. If there is it eluded me this evening. Therefore my formula just draws the total for each data column and adds them up.

In my search for a better solution I hit upon the formula below which is using SUM instead of SUMPRODUCT. Unfortunately, it has the same drawback but seems to handle it a little smarter.

=SUM(SUMIFS(Amounts,Data1,Codes),SUMIFS(Amounts,Data2,Codes))

Note that this is an array formula. It must be committed with Shift+Control+Enter (in place of the simple Enter for normal formulas). Just type or paste the formula in the Formula Bar as usual but don't hit Enter when you are done typing. Press Shift+Control+Enter instead. When confirmed correctly Excel will dispaly the formula surrounded by curly braces in the Formula Bar. Else, there will be a #VALUE error.

This may tilt the balance to let you favour the SUMPRODUCT variety. Actually, SUMPRODUCT is perhaps the only worksheet function which naturally handles arrays, a capability otherwise reserved for array formulas.

Discuss

Discussion

Thanks.  When I get the opportunity I will try it out on my full data.  As you recommended I am using named ranges with it, which does indeed make it a lot easier.
pduffer Oct 17, '18 at 4:34 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login