200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
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.
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.
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.