needforvba
New Member
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....
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....