Can anyone tell me what is wrong with this formula please?!

[Guest]

=SUMIF('Hourly Conversion'!$C:$C,Calculations!$A:$A,(AVERAGE('Hourly
Conversion'!$L:$L)))

 

[Bernard Liengme]

What are you trying to do? You seem to want an average but the criteria is
unclear.
This is how SUMIF works =SUMIF(range1,test,range2)
It compares each element in range1 to the test and then adds the
corresponding cells in range2 (or in range1 if the third argument is
omitted)

Your formula looks at the data in 'Hourly Conversion'!$C:$C
It should compare each of these to a single value; your formula has an array
Calculations!$A:$A
Your last argument is not a range but a function

How about SUMIF(range1,test,range2)/COUNTIF(range1,test)
best wishes

 

[Guest]

Bernard,

From what you have said I think SUMIF may not be the right function. I am
trying to get an average of all cells in 'Hourly Conversion' that match the
same criteria as those cells in 'Calculations' (April) i.e.

The following data is from 'Hourly Conversion' and I wish to take an average
of all the times recorded in April and present the result in the Calculations
worksheet.

Hourly Conversion
April 00:45:02
May 00:00:00
April 00:10:02
January 00:00:02
November 01:00:02
April 00:00:35
May 01:45:52
January 00:45:49
May 00:19:41

Cheers,

 

[Bernard Liengme]

Assuming the month names are in column A and the times in B, then
=SUMIF('Hourly Conversion'!$A:$A,"April", 'Hourly Conversion'!$B:$B)
will SUM all the times for B cells whose corresponding A cell has April
If your Calculations worksheet has the word April in cell A1 then
=SUMIF('Hourly Conversion'!$A:$A, A1, 'Hourly Conversion'!$B:$B)
will do the same
Likewise
=COUNTIF('Hourly Conversion'!$A:$A,"April", 'Hourly Conversion'!$B:$B) or
=COUNTIF('Hourly Conversion'!$A:$A, A1, 'Hourly Conversion'!$B:$B)
will COUNT these cells.
And SUMIF()/COUNTIF() will give the average
best wishes