i need formula to calculate sales of sum,count,AVERAGE

[breadwinner]

dear all

i need a help on excel formula to calculate from 2 way value as look up

i have set of sales data from day 1 to 14 days for 2 companies into range

i need a formula to calculate across wizard of row(dealer name) and column (Item Name)

find the attachment

Thanks
jack

[AliGW]

Why have you posted in the VBA section? Was this a mistake? If so, I can move the formula for you.

[breadwinner]

yes it need to be formula section

[JeteMc]

Perhaps this will help.
1. For Total Count of Sales: =SUMPRODUCT(($B$5:$O$5=B$25)*(ISNUMBER($B7:$O7)))
2. For Total Sales**: =SUM(IFERROR(($B$5:$O$5=B$25)*($B7:$O7),0))
3. For Total Nil: =SUMPRODUCT(($B$5:$O$5=B$25)*($B7:$O7="nil"))
4. For Total Returns: =SUMPRODUCT(($B$5:$O$5=B$25)*($B7:$O7="return"))
5. For Average: =C29/B29
Note that row 25 is populated using: =IF(B26<>"",TRIM(LEFT(B26,SEARCH("sales",B26)-1)),A25)
**Denotes an array formula which is not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
Let us know if you have any questions.

[breadwinner]

Hi Experts,excuse me for delay reply Thanks for formula, its works perfectly 100%

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.