iferror and =aggregate formulas not working

[Hopey87]

Morning,

I am trying to add hours however some cells have #N/A (this is due to a formula already there awaiting data). I have used =AGGREGATE(9,6,E4:E34) and =SUM(IF(ISNA(E4:E34),0,E4:E34)) however they both are equalling 0, even though there are hours. total hrs have a formula =TEXTAFTER(B4, "x")

 

[awoohaw]

Does this work for you:

Book1
	A	B	C	D	E	F
1	December	Rostered	Total Hours	Actual	Total Hours	Additional Hours
2	2023-12-01	8x8.5	8.5	8x8.5	8.5
3	2023-12-02	8x8.5	8.5		#N/A	#N/A
4	2023-12-03		#N/A		#N/A	#N/A
5	2023-12-04		#N/A		#N/A	#N/A
6	2023-12-05		#N/A		#N/A	#N/A
7	2023-12-06		#N/A		#N/A	#N/A
8	2023-12-07		#N/A		#N/A	#N/A
9	2023-12-08		#N/A		#N/A	#N/A
10	2023-12-09		#N/A		#N/A	#N/A
11	2023-12-10		#N/A		#N/A	#N/A
12	2023-12-11		#N/A		#N/A	#N/A
13	2023-12-12		#N/A		#N/A	#N/A
14	2023-12-13		#N/A		#N/A	#N/A
15	2023-12-14		#N/A		#N/A	#N/A
16	2023-12-15		#N/A		#N/A	#N/A
17	2023-12-16		#N/A		#N/A	#N/A
18	2023-12-17		#N/A		#N/A	#N/A
19	2023-12-18		#N/A		#N/A	#N/A
20	2023-12-19		#N/A		#N/A	#N/A
21	2023-12-20		#N/A		#N/A	#N/A
22	2023-12-21		#N/A		#N/A	#N/A
23	2023-12-22		#N/A		#N/A	#N/A
24	2023-12-23		#N/A		#N/A	#N/A
25	2023-12-24		#N/A		#N/A	#N/A
26	2023-12-25		#N/A		#N/A	#N/A
27	2023-12-26		#N/A		#N/A	#N/A
28	2023-12-27		#N/A		#N/A	#N/A
29	2023-12-28		#N/A		#N/A	#N/A
30	2023-12-29		#N/A		#N/A	#N/A
31	2023-12-30		#N/A		#N/A	#N/A
32	2023-12-31		#N/A		#N/A	#N/A
33
34			17		8.5
Sheet5

Cell Formulas
Range	Formula
C2:C32,E2:E32	E2	=1*TEXTAFTER(D2,"x")
F3:F32	F3	=NA()
C34,E34	C34	=SUM(IFERROR(C2:C32,0))

 

[Hopey87]

do you know why its rounding the number up? I used those formulas?

 

[awoohaw]

I can't tell because you posted a picture. But, I can reasonably guess you have the cells formatted as number with zero decimal. Or you're converting the number to text using text with no decimal. Try formatting both of those columns to general and see what happens.

 

[Hopey87]

THANK YOU.
Hopefully last issue. cell is MTx8 I want to count the hours (8) after all cells that start with MTx ive tried the above formula to count after the x but if comes with a SPILL error

 

[Peter_SSs]

Why not get rid of all those error values? It would make your sheet look a lot better & makes the 'sum' cell formulas simpler.
Also, you can do this with only formulas in the top cells rather than copying each one down the column.

Guessing a bit about what formulas are where, but something like this.

24 03 12.xlsm
	B	C	D	E	F
1	Rostered	Total Hours	Actual	Total Hours	Additional Hours
2	8x8.5	8.5	8x8.5	8.5	0
3	8x8.5	8.5
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34		17		8.5	0
Hopey87

Cell Formulas
Range	Formula
C2:C32,E2:E32	C2	=IFNA(1*TEXTAFTER(B2:B32,"x"),"")
F2:F32	F2	=IF(E2:E32="","",E2:E32-C2)
C34,E34:F34	C34	=SUM(C2:C32)
Dynamic array formulas.

Hopefully last issue. cell is MTx8 I want to count the hours (8) after all cells that start with MTx ive tried the above formula to count after the x but if comes with a SPILL error

I don't see any data like that in your samples above. Could we have a small set of sample data and the expected results with XL2BB so we can see the layout and easily copy for testing without having to manually type data?

 

[awoohaw]

THANK YOU.
Hopefully last issue. cell is MTx8 I want to count the hours (8) after all cells that start with MTx ive tried the above formula to count after the x but if comes with a SPILL error

what is the formula you had? Do you want the value to be in a number of hours (a number format)? Or Elapsed hours (a time format)?
Please post a mini of your worksheet using the xl2bb add in (link below) (or post a table that can be copied and the formulas that you have).