Creating a formula that uses the results of another formula gives me a #Value error

[ReaRaeS]

My formula in cell J30 =SUMIFS(H:H, J28,">8", G:G,"p",I:I,">8") gives me the #Value error because the I column is all formulas. "=IF(G27<>"",H27+I26,0)" starting on I2 all the way down. a sample of the spreadsheet is below.

7:45 AM​	oh	0.25​	0.25​
8:00 AM​	oh	0.25​	0.5​
8:15 AM​	t	0.25​	0.75​
8:30 AM​	t	0.25​	1​
8:45 AM​	t	0.25​	1.25​
9:00 AM​	p	0.25​	1.5​
9:15 AM​	p	0.25​	1.75​
9:30 AM​	p	0.25​	2​
9:45 AM​	p	0.25​	2.25​
10:00 AM​	p	0.25​	2.5​
10:15 AM​	p	0.25​	2.75​
10:30 AM​		0.25​	0​
10:45 AM​		0.25​	0​
11:00 AM​		0.25​	0​	0.5​	Overhead hours
11:15 AM​		0.25​	0​	0.75​	Travel hours
11:30 AM​		0.25​	0​	1.5​	Prevailing Wage
11:45 AM​		0.25​	0​	0​	Regular Project
12:00 PM​		0.25​	0​
12:15 PM​		0.25​	0​
12:30 PM​		0.25​	0​	2.75​	Total hours for day
12:45 PM​		0.25​	0​
1:00 PM​		0.25​	0​		Prevailing Wage OT
1:15 PM​		0.25​	0​
1:30 PM​		0.25​	0​

 

[Fluff]

You are getting the error because all the ranges must be the same size, but you have 3 ranges that are entire columns & 1 that is a single cell.

 

[ReaRaeS]

I thought that criteria in ifs statements didn't all need to be ranges. as long as the criteria was specific. It should only run the sum if the first criteria is that the total in cell j28 is greater than 8, then look in column g for p's where the total is over 8 in column I so in this instance it wouldn't run the formula because the total in J28 is only 2.75. I have never had it not run a sumif based on a single cell criteria before.

 

[Fluff]

From the Microsoft site

The Criteria_range argument must contain the same number of rows and columns as the Sum_range argument.

SUMIFS function - Microsoft Support

How to use the SUMIFS function in Excel, one of Excel’s math and trig functions, to add arguments that meet multiple criteria.

support.microsoft.com