Non Working SUMIF (more than 1 criteria)

Normally, sumif formula works well with problems that have one criteria to take into consideration.

With the table that I have, I'm unable to accurately use SUMIF. I'm not even sure if I should use it since what I really need is Average. SUMProduct proves to be even harder to use for me.

Here's what I have:

ID CODE Status START DUE END TURN AROUND TIME (TAT)
122307 Closed 1/14 2/8 1/31 12 DAYS
122265 Closed 1/14 2/2 2/2 14 DAYS
122265 Closed 1/14 2/10 1/31 12 DAYS
122307 ATD 1/13 1/21 --- -------
122253 Closed 1/12 1/22 1/22 9 DAYS
122265 ATD 1/22 1/30 --- -------


Other details:

I used =SUMPRODUCT(--($B$21:$B$31=C4),--($D$21:$D$31=$D$3)) to count the total number of a specific task status per ID code, say, closed and ATD.

I manually added TAT that has "Closed" status on a per ID code basis.

Ex:

ID Code # of Closed Status Average TAT
12265 2 13 DAYS (14+12/2)


Is there a formula that will give me the average TAT taking ID Code and "Closed" status as conditions/criteria? What about Vlookup, average?

Thanks in advance.

try using this in e4 of your sample:
=AVERAGE(IF($B$17:$B$22=C4,IF($C$17:$C$22=$D$3,$G$17:$G$22)))
confirmed with ctrl+shift+enter

I would also suggest in addition to the prior post that you consider using a Pivot Table.

Push the data in B16:G22 into a Pivot and set up such that:

Please Login or Register  to view this content.

If you have large data volume and / or lots of calcs to perform this will be most efficient route open to you.

Worth adding that you can add TAT field multiple times to the DATA Field in the PT - ie once to show AVERAGE, once to show SUM, once to show MAX, once to show MIN etc etc...