Saariko Wrote:
[...]
I have 3 colums: A-dealer B-Date C-sale
I want to sum (Pivot Table is not suitable for this part, I use it on
a
different worksheet) All sales made be a dealer based on a month.
Asia 9/28/2004 2134
Asia 9/30/2004 2136
Europe 10/1/2004 2137
Europe 10/4/2004 2138
Europe 10/12/2004 2139
Europe 10/12/2004 2140
Asia 10/17/2004 2141
Europe 10/18/2004 2142
The sum would go to a different sheet
MONTH Dealer Total Sales
September Asia ?????
October Europe ?????
[...]
Let A1:D9 on Sheet1 house the dealer data.
Let A2:B2 on Sheet2 house the conditions of interest.
In C2 on Sheet2 enter & copy down:
=SUMPRODUCT(--(TEXT(Sheet1!$B$2:$B$9,"dddd")=A2),--(Sheet1!$A$2:$A$9=B2),Sheet1!$C$2:$C$9)
if the year must be explicitly excluded.
Otherwise, ensure that A2 houses a true date, 9/1/2004, formatted to
show, for example: Sep-04 using mmm-yy as custom format. Change the
formula to include the year test as follows:
=SUMPRODUCT(--(TEXT(Sheet1!$B$2:$B$9,"mmm-yy")=TEXT(A2,"mmm-yy"),--(Sheet1!$A$2:$A$9=B2),Sheet1!$C$2:$C$9)
--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile:
http://www.excelforum.com/member.php...fo&userid=4165
View this thread:
http://www.excelforum.com/showthread...hreadid=222618