Product based on criteria

[zvukvnochi]

Hello. I need help with creating a formula that will calculate the product for a month based on the average rate for that month and the count. The count needs to be dynamic in this formula, i.e., if the cell value of the month is empty, the product formula would need to ignore the value in the count column.


Is this doable? Thank you in advance for any help/guidance!


Here’s the link to a sample workbook. The cells highlighted in yellow I manually added: for June the product takes the average rate and multiplies it by 3 because all cells for June are populated; for July the average rate is multiplied by 2.


https://rapidshare.com/files/2840056393/product_workbook.xlsx

 

[Hui]

Zvukvnochi


C10: =SUMPRODUCT($B2:$B4,C2:C4)

copy across


Your existing formula in C8 is incorrect

If Mangoes are 0, =AVERAGE(C2:C4) will be 2860 which is the average of 2050, 6532 and 0

but it should be average of 2050 + 6532 only = 4291

 

[zvukvnochi]

Thank you, Hui!

 

[zvukvnochi]

Hi. So, I tried this formula and it works great if the count is equal to 1, but doesn't work if the count is either less than or greater than 1. Can this be resolved somehow? Thanks again for the help!

 

[Hui]

Change the Average formula

C8:

=AVERAGEIFS(C$2:C$4,$B$2:$B$4,">0")

Change the Product formula

C10: =C8*SUM(B2:B4)

Copy across