SUMIF(AND) FUNCTION

[KL]

Here are my comments:

=SUMPRODUCT(--(A3:A10000="Asia"),--(B3:B10000="9/28/2004"))
Won't work because "9/28/2004" is text not Date Value for Excel. You should
use date serial number, in this case 38258, so that the formula looks as
follows =SUMPRODUCT(--(A3:A10000="Asia"),--(B3:B10000=38258)). The formula
only COUNTS(!!!) exact DATE matches where DEALER is "Asia".

=SUMPRODUCT(--(A3:A10000="Asia"),--(MONTH(B3:B10000)=10))
Works like charm to me. See if list separator on your system is set to ";"
and then change "," by ";".
The formula only COUNTS(!!!) exact MONTH matches where DEALER is "Asia".

=SUMPRODUCT(--(A3:A10000="Asia"),--(MONTH(B3):MONTH(B10000)=10))
Wrong sintax. MONTH(B3):MONTH(B10000)=10 makes no sense and will not work.

what you probably need is the following:
=SUMPRODUCT($C$3:$C$1000,--($A$3:$A$10000="Asia"),--(MONTH($B$3:$B$10000)=10))
The formula SUMS(!!!) SALES, where there are exact MONTH matches combined
with "Asia" for DEALER.

 

[Aladin Akyurek]

[...]

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 o
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 A19 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 t
show, for example: Sep-04 using mmm-yy as custom format. Change th
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

 

[Govind]

Hi,

You can use

=SUMPRODUCT((A170:A180="Asia")*(MONTH(B170:B180)=9)*(C170:C180))

Regards

Govind.