Sumifs with dynamic criteria range

[Rhodes19]

Hi there, feel like there might be a simple way to do this, but I'm stumped. I'm looking for a way to have a sumifs criteria range tie to a reference rather than be static. Here's an example table (image attached as well, just in case), and my problem outlined below:

Thanks for your help!

Example Table:
Sq. Ft.	2022​	2023​	2024​
85,783​	0​	8​	11​
5,837​	6​	0​	7​
6,070​	21​	44​	0​
261,674​	0​	5​	17​
7,575​	98​	12​	3​

Desired Outcome:

Create three sums of column A data, one for each year, summing only those data points that are greater than zero in the respective year

For example, 2022 should be A5+A6+A8, for a total of 19,482

Problem:

I want the formula to search within cols B-D and sum the correct year based on a reference, rather than me having to manually change the column in the formula

For example, for the 2022 total, I would want to replace the absolute reference in the below formula (B4:B8) with something dynamic
=SUMIFS(A4:A8,B4:B8,">"&0)

Also Tried:

Using IF(COUNTIF to look for a value within the range (eg 2022), but then I don't know how to tell it to do the sumifs only for the columns that contain that value

 

[Ezguy4u]

Rhodes Let's get the ball rolling. If this is the solutions to your problem, than good. If there are more questions than let us know.

Book1
	A	B	C	D
1	Sq.Ft	2022	2023	2024
2	85.783	0	8	11
3	5.837	6	0	7
4	6.07	21	44	0
5	261.674	0	5	17
6	7.575	98	12	3
7		19.482	361.102	360.869
Data

Cell Formulas
Range	Formula
B7:D7	B7	=SUMIFS($A$2:$A$6,B2:B6,">0")

 

[Rhodes19]

Rhodes Let's get the ball rolling. If this is the solutions to your problem, than good. If there are more questions than let us know.

Book1
	A	B	C	D
1	Sq.Ft	2022	2023	2024
2	85.783	0	8	11
3	5.837	6	0	7
4	6.07	21	44	0
5	261.674	0	5	17
6	7.575	98	12	3
7		19.482	361.102	360.869
Data

Cell Formulas
Range	Formula
B7:D7	B7	=SUMIFS($A$2:$A$6,B2:B6,">0")

Thanks for answering! However, that's basically the same formula I already have, and what I want to do is NOT have to hardcode B2:B6 (or C2:C6, etc). The file this comes from is more complex than this simplified example. The data output is on a different tab with a list of years, and I want to reference those cells in my formulas, as the reference years will change, though the data table will not.

Let me know if that explains it better!

 

[Ezguy4u]

Ok Rhodes, Now I am going to assume those are the correct totals you want to see. If that is true than we can move on to your next criteria the data output is on a different tab. As you can see below, we can reference a different tab in the formula. As far as the hardcoding, if we can hold off on that until you are happy with the different tab criteria, I am sure we can solve that problem. Again this usually generates more questions than answers so, let the discussion begin.

Book1
	A	B	C	D
1	Sq.Ft	2022	2023	2024
2	85.783
3	5.837
4	6.07
5	261.674
6	7.575
7		19.482	361.102	360.869
Data 1

Cell Formulas
Range	Formula
B7:D7	B7	=SUMIFS($A$2:$A$6,'Data 2'!B2:B6,">0")

Book1
	A	B	C	D
1	Sq.Ft	2022	2023	2024
2		0	8	11
3		6	0	7
4		21	44	0
5		0	5	17
6		98	12	3
Data 2

 

[Peter_SSs]

See if either of these options are of interest.

BTW, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

23 07 29.xlsm
	A	B	C	D	E	F	G	H	I
1							Totals
2		2022	2023	2024			2022	2023	2024
3	5000	0	8	11			14000	216000	215000
4	3000	6	0	7			14000	216000	215000
5	4000	21	44	0
6	200000	0	5	17
7	7000	98	12	3
Yearly Sums

Cell Formulas
Range	Formula
G3:I3	G3	=SUMIFS($A3:$A7,INDEX($B3:$D7,0,MATCH(G2,$B2:$D2,0)),">0")
G4:I4	G4	=SUM(FILTER($A3:$A7,FILTER($B3:$D7,$B2:$D2=G2)>0))