I am trying to figure out how to sum the average on 2 separate ranges based
on the user selection of the month. This is the way the data is setup. The
first row is the column headers and I have the row numbers going down.
A B C
1 Jan05 Paid 5
2 Feb05 Paid 8
3 Mar05 Paid 4
4 Jan05 Not Paid 6
5 Feb05 Not Paid 3
6 Mar05 Not Paid 7

I need to know the sum of the averages of the paid and not paid when the
user selects the month. So, if the user selects Feb05, need to know the sum
average of paid and not paid. This is the beginning of the formula, but need
help completing it:
=Sum(Average(index C1:C6,match(1,...not sure what to do from here
Thanks in advance.

Try:
=sumproduct(--(A1:A6=month),--(b1:b6="paid"),c1:c6)/sumproduct(--(A1:A6=month),--(b1:b6="paid"))

where "month" is a reference to the cell with the users' choice of month


"William" wrote:

I am trying to figure out how to sum the average on 2 separate ranges based
on the user selection of the month. This is the way the data is setup. The
first row is the column headers and I have the row numbers going down.
A B C
1 Jan05 Paid 5
2 Feb05 Paid 8
3 Mar05 Paid 4
4 Jan05 Not Paid 6
5 Feb05 Not Paid 3
6 Mar05 Not Paid 7

I need to know the sum of the averages of the paid and not paid when the
user selects the month. So, if the user selects Feb05, need to know the sum
average of paid and not paid. This is the beginning of the formula, but need
help completing it:
=Sum(Average(index C1:C6,match(1,...not sure what to do from here
Thanks in advance.

H2 contains data e.g. Feb 05 (01/2/05 in ddmm/yy)

=AVERAGE(IF($A$2:$A$7=H2*($B$2:$B$7="Paid"),$C$2:$ C$7))+AVERAGE(IF($A$2:$A$7=H2*($B$2:$B$7="Unpaid") ,$C$2:$C$7))

Enter with Ctrl+Shift+Enter

"Duke Carey" wrote:

Try:
=sumproduct(--(A1:A6=month),--(b1:b6="paid"),c1:c6)/sumproduct(--(A1:A6=month),--(b1:b6="paid"))

where "month" is a reference to the cell with the users' choice of month


"William" wrote:

I am trying to figure out how to sum the average on 2 separate ranges based
on the user selection of the month. This is the way the data is setup. The
first row is the column headers and I have the row numbers going down.
A B C
1 Jan05 Paid 5
2 Feb05 Paid 8
3 Mar05 Paid 4
4 Jan05 Not Paid 6
5 Feb05 Not Paid 3
6 Mar05 Not Paid 7

I need to know the sum of the averages of the paid and not paid when the
user selects the month. So, if the user selects Feb05, need to know the sum
average of paid and not paid. This is the beginning of the formula, but need
help completing it:
=Sum(Average(index C1:C6,match(1,...not sure what to do from here
Thanks in advance.

Thank you to all. This forum is great for getting the answers you need.

"Toppers" wrote:

H2 contains data e.g. Feb 05 (01/2/05 in ddmm/yy)

=AVERAGE(IF($A$2:$A$7=H2*($B$2:$B$7="Paid"),$C$2:$ C$7))+AVERAGE(IF($A$2:$A$7=H2*($B$2:$B$7="Unpaid") ,$C$2:$C$7))

Enter with Ctrl+Shift+Enter

"Duke Carey" wrote:

Try:
=sumproduct(--(A1:A6=month),--(b1:b6="paid"),c1:c6)/sumproduct(--(A1:A6=month),--(b1:b6="paid"))

where "month" is a reference to the cell with the users' choice of month


"William" wrote:

I am trying to figure out how to sum the average on 2 separate ranges based
on the user selection of the month. This is the way the data is setup. The
first row is the column headers and I have the row numbers going down.
A B C
1 Jan05 Paid 5
2 Feb05 Paid 8
3 Mar05 Paid 4
4 Jan05 Not Paid 6
5 Feb05 Not Paid 3
6 Mar05 Not Paid 7

I need to know the sum of the averages of the paid and not paid when the
user selects the month. So, if the user selects Feb05, need to know the sum
average of paid and not paid. This is the beginning of the formula, but need
help completing it:
=Sum(Average(index C1:C6,match(1,...not sure what to do from here
Thanks in advance.