Not sure why SUMIFS isn't working here...

[mysteryg45]

I have a sheet ("Form Responses 1") listing Date 1 and Date 2 and calculating the amount of time between them. And then in another sheet I also want to average the amount of time between Date 1 and Date 2, where Date 2 (in column H) is only in the last 6 months. (For some reason I couldn't get AVERAGEIF to work, so I'm using SUMIF/COUNTIF.) The following formula returns 0. Where am I going wrong? (I have a feeling it's to do with how I've written out the check for if the date is in the last 6 months...)

=SUMIF('Form Responses 1'!H:H,">"&TODAY()-180,'Form Responses 1'!L:L)/COUNTIF('Form Responses 1'!H:H,">"&TODAY()-180)

 

[DanteAmor]

Hi and welcome to MrExcel

How about:

Libro2
	H	I	J	K	L	M	N	O
1
2
3
4	24/06/2023				20			40
5
6	04/07/2023				60
Form Responses 1

Cell Formulas
Range	Formula
O4	O4	=AVERAGEIF('Form Responses 1'!H:H,">"&TODAY()-180,'Form Responses 1'!L:L)

 

[mysteryg45]

Hi and welcome to MrExcel

How about:

Libro2
	H	I	J	K	L	M	N	O
1
2
3
4	24/06/2023				20			40
5
6	04/07/2023				60
Form Responses 1

Cell Formulas
Range	Formula
O4	O4	=AVERAGEIF('Form Responses 1'!H:H,">"&TODAY()-180,'Form Responses 1'!L:L)

Like I said, that's not working -- it's giving me a #DIV/0! error. That's why I'm doing COUNTIF/SUMIF.

 

[mysteryg45]

The whole sheet is a fair deal more complicated than what I've presented here, so maybe pictures will help with a solution? There's probably something also to why AVERAGEIFS errors out. I'm just not sure if I'm missing some syntactical thing or just hindered by what I don't know about Excel...

The Form Responses 1 sheet

The other sheet that's pulling data from Form Responses 1:

 

[Peter_SSs]

Welcome to the MrExcel board!
Is this a Google Sheets question?