average duration for canceled bills

Hi, I need a formula calculating the average just for canceled bills.

i have #3 columns:
#1 to identify the client
#2 p=pending c= canceled
#3 #of days on pending until bill is canceled.

I need to know the average # of days of canceled bills per each client.

please see dummy file attached.

thank you very much for your help.

carlo

Hi Carlo, you may want to try:

=AVERAGE(IF(($A$4:$A$14=A18)*($B$4:$B$14="c"),$C$4:$C$14))

This is an array formula, so you must confirm using CTRL+SHIFT+ENTER, not just ENTER.

Or you could use a pivot table.......


Row Labels Average of days
1 13
c 16
p 3
2 8
c 6
p 12
3 6
c 8
p 2
4 12
c 12
(blank)
(blank)
Grand Total 10