Summary of the expenses.

[needforvba]

hi forum,

i m facing some problem with the formula in my worksheet.i had a worksheet with the following data.

Date Month Sales Man Amount

01/4/11 Apr'11 A 1504

01/4/11 Apr'11 B 1425

01/5/11 May'11 A 1400

01/5/11 May'11 B 1409

01/6/11 Jun'11 A 1580

01/6/11 Jun'11 A 1250


I had built a table with the format:


DESIRED TABLE:##

Sales Man Apr'11 May'11 Jun'11

A 1504 1400 1580

B 1425 1409 1250


I know the same can be achieved using pivot table..but i want to do the same using a formula.For that i tried with Sumproduct. i used Name for the ranges for achieving the same.

For example:

Amt for the Amount Column in the main table/data

s_man for the salesman Column in the main table/data

Mon for the Month Column in the main table/data


FORMULA USED IN THE DESIRED TABLE

=SUMPRODUCT(s_man=$A$2)*(Mon=B1)*(amt)


i need sumproduct to show the total amount the salesman A had made in Apr month.similar with the other salesman B in all the months...

The result i m getting is :0 in all the cells in the desired table....


Please tell me where i m going wrong....

 

[needforvba]

How to do the same with the pivot table dynamically upon adding new data in the SAME FORMAT.

 

[dan_l]

Where your data is a1-d6 and your output table is g4-j6


=SUMPRODUCT(($B$1:$B$6=H$3)*($C$1:$C$6=$G4)*($D$1:$D$6))


Best way to get a pivot table to do it dynamically:

Excel 07/10: store your data in a table, make the pivot table's source, refresh the pivot table after you add new data

Excel 03: set up a dynamic range around your data, use the dynamic range as your pivot table source, refresh the pivot table after you add new data.

 

[needforvba]

dear dan.thanks for the reply.I had written the same formula using NAMES for ranges.but i did not understand where i went wrong....

 

[needforvba]

any replies please....

 

[NARAYANK991]

Hi ,


You need to enter the formula as an array formula ( with CTRL SHIFT ENTER ) :


=SUMPRODUCT(($C$3:$C$8=J$2)*($D$3:$D$8=$I3)*($E$3:$E$8))


where C3:C8 are the months , J2:L2 are the months ; D3:D8 are the Salesman names , I3:I4 are the Salesman names ; E3:E8 are the amounts.


Note that the amount against Salesman B in June will be 0 , not 1250.


Narayan