CODEWITHSUNDEEP
excelexcel-formulaaverage
Secret Squirrel
Secret Squirrel

Reputation: 179

AVERAGEIF with multiple criteria

I've seen a lot of similar questions on here, but nothing that answers what I'm specifically after.

I have a bunch of columns of contact information. Column A has the date of contact (i.e 01/11/2019). Column B has the call start time (ie. 13:42). Column D has the call end time (i.e 13:58).

I want to work out the average minutes of a call for a specific date. So to get average time spent on a call on 01/11/2019, I need a formula that searches for all instances of 01/11/2019 in column A, then for each instance get the time in column D minus the time in column B, then give me an average for all of these.

So far I have:

    =AVERAGEIFS(A:A,"01/11/2019",(=D:D-B:B),>0))

Which I assumed would give me an average if column A has 01/11/19, and the result of column D minus column B is more than 0. But it doesn't.

Please help!

Upvotes: 0

Views: 341

Answers (2)

JvdV
JvdV

Reputation: 75850

You can't incorporate an array of values into AVERAGEIF, nor AVERAGEIFS as they expect Ranges. However, you could re-create AVERAGEIF quite easily:

enter image description here

So the formula in G2 to re-create AVERAGEIF could be:

=AVERAGE(IF(A2:A5=G1,D2:D5-B2:B5))

Note: This formula is an array formula and needs to be confirmed through CtrlShiftEnter.

Effectively this is an array (heavy on calculation, and if you wouldn't want to enter it as such, you could replace it with:

=SUMPRODUCT((A2:A5=G1)*(D2:D5-B2:B5))/COUNTIF(A2:A5,G1)

Note: Column C is hidden in this example.

Upvotes: 4

mohit
mohit

Reputation: 1

Column A contains Date, Column B Start Time , column C End Time, COlumn D ( Start time - End time) , Use formula>> SUMIFS($D$2:$D$8,$A$2:$A$8,$H$3)/COUNTIF($A$2:$A$8,H3) where H3 is the date

Upvotes: 0

Related Questions