SUMIFS with Range Determined by Formula

Graemea

Board Regular
Joined
Oct 30, 2015
Messages
119
Office Version
  1. 365
Platform
  1. Windows
Hi,


I want to use the SUMIFS function to add the values in $AS$1:$AS$2000 based on the criteria_range $A$1:$A$2000 and criterion in cell AZ1.


Rather than selecting the sum_range and criteria_range directly (e.g. by typing in or selecting the range with the mouse) I would like to build these references using formulae.


This is important because the sum_range may not always be in column AS.


The column for the sum_range will be determined by its heading - so, for example, the data with the required heading may be in column AS this month and AV the next, so I would like to make the references dynamic.


Can someone please suggest a solution.


I have tried ADDRESS and OFFSET without success.


Thanks!
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Function SUMIFS does not allow the Range argument to be anything but a range. Here is one way to acheive what you're after.

The cells H1 and H2 contain the criteria (which I populated with Data Validation). The formula sums up the matching data for the two criteria, as indicated by the orange background in the table of data. Change the criteria and the summation will change.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
Book1
ABCDEFGH
1BistroBob's
2DinnerBeef
3BistroBaconBeefChickenLambSum:187
4Abe's13373092
5Bob's58186664
6Cal's76439672
7Bob's771453
8Abe's46445597
9Abe's35628229
10Cal's86899687
11Cal's8829532
12Abe's9741282
13Bob's59983251
Sheet28
Cell Formulas
RangeFormula
H3=SUMPRODUCT(INDEX(B4:E13,,MATCH(H2,B3:E3,0))*(A4:A13=H1))
[/FONT]
 
Upvote 0

Forum statistics

Threads
1,223,239
Messages
6,170,947
Members
452,368
Latest member
jayp2104

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top