Hi Frank,

I usually use your replies with blind eyes, and Bingo. However, I have tried
this solution of multiple arrays and condition, and a little problem for me
on this one.

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 ?????

Again, I can't use pivot table for this part.

I tried to tweak your funciton:
=SUMPRODUCT(--(A3:A10000="Asia"),--(B3:B10000="9/28/2004"))
=SUMPRODUCT(--(A3:A10000="Asia"),--(MONTH(B3:B10000)=10))
=SUMPRODUCT(--(A3:A10000="Asia"),--(MONTH(B3):MONTH(B10000)=10))

but no luck.

Can you help?

"Frank Kabel" wrote:

Hi
first don't replace the '--'. They coerce the boolean values to real
numbers (TRUE=1/FALSE=0)
for your example if you want to get the sum for one specific account
use
=SUMPRODUCT(--(A1:A4000=account_number),--(C1:C4000="collections"),--(D
1:D4000="michigan"),F1:F4000)

Note: this is case sensitive. You may also consider using a pivot table
for this:
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm


--
Regards
Frank Kabel
Frankfurt, Germany


dave wrote:
Frank,

Sorry, I got an error. What do I put in where the "--"
are?

Here is a more accurate example of my worksheet.

Column A lists a range of account numbers so I'd like this
range to find all 4000 accounts.

Column C lists a range of departments so I'd like to
find "collections"

column D lists the location so I want "michigan"

Column F has the balances so I want the sum of every
account that meets these 3 criteria.

Thanks,

Dave
-----Original Message-----
Hi
use SUMPRODUCT instead. e.g.
=SUMPRODUCT(--(A1:A100=100),--(B1:B100="manager"),C1:C100)

--
Regards
Frank Kabel
Frankfurt, Germany


Dave Gremaud wrote:
Does anyone use this function or have any suggestions on a
different function that may work. I have a very large
worksheet with multiple data columns. I am trying to sum
multiple cells by using multiple variables. The SUMIF
function works well but I can't insert more than one
criteria. I have tried many times without success. I
would greatly appreciate any assistance.

Here is an example of my need:

I would like a function to read (if column "a" = 100 and
column "b" = "manager" then sum these fields).

I know there must be a solution but it escapes me.

Thanks!

Dave
.

=SUMPRODUCT((A1:A8="Europe")*(MONTH(B1:B8)=10)*(C1 :C8))

should do it


--
Alex Delamain
------------------------------------------------------------------------
Alex Delamain's Profile: http://www.excelforum.com/member.php...o&userid=11273
View this thread: http://www.excelforum.com/showthread...hreadid=222618

Hi,

How about if you add a column after the date and include the formula
MONTH(A2) that returns the month number then use the following formula:

A = dealer
B = date
C = month number
D = sale

=SUMPRODUCT(--(A2:A9=A13),--(C2:C9=B13),D2:D9)

You'd then need to enter 9 instead of September in you other sheet as the
matching criteria.

Good luck, Rob

"Saariko" wrote in message
...
Hi Frank,

I usually use your replies with blind eyes, and Bingo. However, I have
tried
this solution of multiple arrays and condition, and a little problem for
me
on this one.

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 ?????

Again, I can't use pivot table for this part.

I tried to tweak your funciton:
=SUMPRODUCT(--(A3:A10000="Asia"),--(B3:B10000="9/28/2004"))
=SUMPRODUCT(--(A3:A10000="Asia"),--(MONTH(B3:B10000)=10))
=SUMPRODUCT(--(A3:A10000="Asia"),--(MONTH(B3):MONTH(B10000)=10))

but no luck.

Can you help?

"Frank Kabel" wrote:

Hi
first don't replace the '--'. They coerce the boolean values to real
numbers (TRUE=1/FALSE=0)
for your example if you want to get the sum for one specific account
use
=SUMPRODUCT(--(A1:A4000=account_number),--(C1:C4000="collections"),--(D
1:D4000="michigan"),F1:F4000)

Note: this is case sensitive. You may also consider using a pivot table
for this:
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm


--
Regards
Frank Kabel
Frankfurt, Germany


dave wrote:
Frank,

Sorry, I got an error. What do I put in where the "--"
are?

Here is a more accurate example of my worksheet.

Column A lists a range of account numbers so I'd like this
range to find all 4000 accounts.

Column C lists a range of departments so I'd like to
find "collections"

column D lists the location so I want "michigan"

Column F has the balances so I want the sum of every
account that meets these 3 criteria.

Thanks,

Dave
-----Original Message-----
Hi
use SUMPRODUCT instead. e.g.
=SUMPRODUCT(--(A1:A100=100),--(B1:B100="manager"),C1:C100)

--
Regards
Frank Kabel
Frankfurt, Germany


Dave Gremaud wrote:
Does anyone use this function or have any suggestions on a
different function that may work. I have a very large
worksheet with multiple data columns. I am trying to sum
multiple cells by using multiple variables. The SUMIF
function works well but I can't insert more than one
criteria. I have tried many times without success. I
would greatly appreciate any assistance.

Here is an example of my need:

I would like a function to read (if column "a" = 100 and
column "b" = "manager" then sum these fields).

I know there must be a solution but it escapes me.

Thanks!

Dave
.

Hi,

If you want the sales for Asia region for the month of September, use

=SUMPRODUCT(--(A3:A10000="Asia"),--(MONTH(B3:B10000)=9),--(C3:C10000))

Regards

Govind.

Saariko wrote:

Hi Frank,

I usually use your replies with blind eyes, and Bingo. However, I have tried
this solution of multiple arrays and condition, and a little problem for me
on this one.

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 ?????

Again, I can't use pivot table for this part.

I tried to tweak your funciton:
=SUMPRODUCT(--(A3:A10000="Asia"),--(B3:B10000="9/28/2004"))
=SUMPRODUCT(--(A3:A10000="Asia"),--(MONTH(B3:B10000)=10))
=SUMPRODUCT(--(A3:A10000="Asia"),--(MONTH(B3):MONTH(B10000)=10))

but no luck.

Can you help?

"Frank Kabel" wrote:


Hi
first don't replace the '--'. They coerce the boolean values to real
numbers (TRUE=1/FALSE=0)
for your example if you want to get the sum for one specific account
use
=SUMPRODUCT(--(A1:A4000=account_number),--(C1:C4000="collections"),--(D
1:D4000="michigan"),F1:F4000)

Note: this is case sensitive. You may also consider using a pivot table
for this:
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm


--
Regards
Frank Kabel
Frankfurt, Germany


dave wrote:

Frank,

Sorry, I got an error. What do I put in where the "--"
are?

Here is a more accurate example of my worksheet.

Column A lists a range of account numbers so I'd like this
range to find all 4000 accounts.

Column C lists a range of departments so I'd like to
find "collections"

column D lists the location so I want "michigan"

Column F has the balances so I want the sum of every
account that meets these 3 criteria.

Thanks,

Dave

-----Original Message-----
Hi
use SUMPRODUCT instead. e.g.
=SUMPRODUCT(--(A1:A100=100),--(B1:B100="manager"),C1:C100)

--
Regards
Frank Kabel
Frankfurt, Germany


Dave Gremaud wrote:

Does anyone use this function or have any suggestions on a
different function that may work. I have a very large
worksheet with multiple data columns. I am trying to sum
multiple cells by using multiple variables. The SUMIF
function works well but I can't insert more than one
criteria. I have tried many times without success. I
would greatly appreciate any assistance.

Here is an example of my need:

I would like a function to read (if column "a" = 100 and
column "b" = "manager" then sum these fields).

I know there must be a solution but it escapes me.

Thanks!

Dave

.

Hi again,

I wen to the xLDunamic help page:
Here is another unsuccessful try:
=SUMPRODUCT((A170:A180="Asia")*(MONTH(ROW(B179:B18 0))=9)*(C170:C180))

please?


"Saariko" wrote:

Hi Frank,

I usually use your replies with blind eyes, and Bingo. However, I have tried
this solution of multiple arrays and condition, and a little problem for me
on this one.

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 ?????

Again, I can't use pivot table for this part.

I tried to tweak your funciton:
=SUMPRODUCT(--(A3:A10000="Asia"),--(B3:B10000="9/28/2004"))
=SUMPRODUCT(--(A3:A10000="Asia"),--(MONTH(B3:B10000)=10))
=SUMPRODUCT(--(A3:A10000="Asia"),--(MONTH(B3):MONTH(B10000)=10))

but no luck.

Can you help?

"Frank Kabel" wrote:

Hi
first don't replace the '--'. They coerce the boolean values to real
numbers (TRUE=1/FALSE=0)
for your example if you want to get the sum for one specific account
use
=SUMPRODUCT(--(A1:A4000=account_number),--(C1:C4000="collections"),--(D
1:D4000="michigan"),F1:F4000)

Note: this is case sensitive. You may also consider using a pivot table
for this:
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm


--
Regards
Frank Kabel
Frankfurt, Germany


dave wrote:
Frank,

Sorry, I got an error. What do I put in where the "--"
are?

Here is a more accurate example of my worksheet.

Column A lists a range of account numbers so I'd like this
range to find all 4000 accounts.

Column C lists a range of departments so I'd like to
find "collections"

column D lists the location so I want "michigan"

Column F has the balances so I want the sum of every
account that meets these 3 criteria.

Thanks,

Dave
-----Original Message-----
Hi
use SUMPRODUCT instead. e.g.
=SUMPRODUCT(--(A1:A100=100),--(B1:B100="manager"),C1:C100)

--
Regards
Frank Kabel
Frankfurt, Germany


Dave Gremaud wrote:
Does anyone use this function or have any suggestions on a
different function that may work. I have a very large
worksheet with multiple data columns. I am trying to sum
multiple cells by using multiple variables. The SUMIF
function works well but I can't insert more than one
criteria. I have tried many times without success. I
would greatly appreciate any assistance.

Here is an example of my need:

I would like a function to read (if column "a" = 100 and
column "b" = "manager" then sum these fields).

I know there must be a solution but it escapes me.

Thanks!

Dave
.

Thanks to all. (Alex, Rob)
Govind's answer has made it happen:-)

Sheers,

Saariko

"Govind" wrote:

Hi,

If you want the sales for Asia region for the month of September, use

=SUMPRODUCT(--(A3:A10000="Asia"),--(MONTH(B3:B10000)=9),--(C3:C10000))

Regards

Govind.

Saariko wrote:

Hi Frank,

I usually use your replies with blind eyes, and Bingo. However, I have tried
this solution of multiple arrays and condition, and a little problem for me
on this one.

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 ?????

Again, I can't use pivot table for this part.

I tried to tweak your funciton:
=SUMPRODUCT(--(A3:A10000="Asia"),--(B3:B10000="9/28/2004"))
=SUMPRODUCT(--(A3:A10000="Asia"),--(MONTH(B3:B10000)=10))
=SUMPRODUCT(--(A3:A10000="Asia"),--(MONTH(B3):MONTH(B10000)=10))

but no luck.

Can you help?

"Frank Kabel" wrote:


Hi
first don't replace the '--'. They coerce the boolean values to real
numbers (TRUE=1/FALSE=0)
for your example if you want to get the sum for one specific account
use
=SUMPRODUCT(--(A1:A4000=account_number),--(C1:C4000="collections"),--(D
1:D4000="michigan"),F1:F4000)

Note: this is case sensitive. You may also consider using a pivot table
for this:
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm


--
Regards
Frank Kabel
Frankfurt, Germany


dave wrote:

Frank,

Sorry, I got an error. What do I put in where the "--"
are?

Here is a more accurate example of my worksheet.

Column A lists a range of account numbers so I'd like this
range to find all 4000 accounts.

Column C lists a range of departments so I'd like to
find "collections"

column D lists the location so I want "michigan"

Column F has the balances so I want the sum of every
account that meets these 3 criteria.

Thanks,

Dave

-----Original Message-----
Hi
use SUMPRODUCT instead. e.g.
=SUMPRODUCT(--(A1:A100=100),--(B1:B100="manager"),C1:C100)

--
Regards
Frank Kabel
Frankfurt, Germany


Dave Gremaud wrote:

Does anyone use this function or have any suggestions on a
different function that may work. I have a very large
worksheet with multiple data columns. I am trying to sum
multiple cells by using multiple variables. The SUMIF
function works well but I can't insert more than one
criteria. I have tried many times without success. I
would greatly appreciate any assistance.

Here is an example of my need:

I would like a function to read (if column "a" = 100 and
column "b" = "manager" then sum these fields).

I know there must be a solution but it escapes me.

Thanks!

Dave

.

Thanks All (Alex, Rob)

I used govinds answer, it worked.

Cheers,

Saariko

"Govind" wrote:

Hi,

If you want the sales for Asia region for the month of September, use

=SUMPRODUCT(--(A3:A10000="Asia"),--(MONTH(B3:B10000)=9),--(C3:C10000))

Regards

Govind.

Saariko wrote:

Hi Frank,

I usually use your replies with blind eyes, and Bingo. However, I have tried
this solution of multiple arrays and condition, and a little problem for me
on this one.

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 ?????

Again, I can't use pivot table for this part.

I tried to tweak your funciton:
=SUMPRODUCT(--(A3:A10000="Asia"),--(B3:B10000="9/28/2004"))
=SUMPRODUCT(--(A3:A10000="Asia"),--(MONTH(B3:B10000)=10))
=SUMPRODUCT(--(A3:A10000="Asia"),--(MONTH(B3):MONTH(B10000)=10))

but no luck.

Can you help?

"Frank Kabel" wrote:


Hi
first don't replace the '--'. They coerce the boolean values to real
numbers (TRUE=1/FALSE=0)
for your example if you want to get the sum for one specific account
use
=SUMPRODUCT(--(A1:A4000=account_number),--(C1:C4000="collections"),--(D
1:D4000="michigan"),F1:F4000)

Note: this is case sensitive. You may also consider using a pivot table
for this:
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm


--
Regards
Frank Kabel
Frankfurt, Germany


dave wrote:

Frank,

Sorry, I got an error. What do I put in where the "--"
are?

Here is a more accurate example of my worksheet.

Column A lists a range of account numbers so I'd like this
range to find all 4000 accounts.

Column C lists a range of departments so I'd like to
find "collections"

column D lists the location so I want "michigan"

Column F has the balances so I want the sum of every
account that meets these 3 criteria.

Thanks,

Dave

-----Original Message-----
Hi
use SUMPRODUCT instead. e.g.
=SUMPRODUCT(--(A1:A100=100),--(B1:B100="manager"),C1:C100)

--
Regards
Frank Kabel
Frankfurt, Germany


Dave Gremaud wrote:

Does anyone use this function or have any suggestions on a
different function that may work. I have a very large
worksheet with multiple data columns. I am trying to sum
multiple cells by using multiple variables. The SUMIF
function works well but I can't insert more than one
criteria. I have tried many times without success. I
would greatly appreciate any assistance.

Here is an example of my need:

I would like a function to read (if column "a" = 100 and
column "b" = "manager" then sum these fields).

I know there must be a solution but it escapes me.

Thanks!

Dave

.

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.


"Saariko" wrote in message
...
Hi Frank,

I usually use your replies with blind eyes, and Bingo. However, I have
tried
this solution of multiple arrays and condition, and a little problem for
me
on this one.

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 ?????

Again, I can't use pivot table for this part.

I tried to tweak your funciton:
=SUMPRODUCT(--(A3:A10000="Asia"),--(B3:B10000="9/28/2004"))
=SUMPRODUCT(--(A3:A10000="Asia"),--(MONTH(B3:B10000)=10))
=SUMPRODUCT(--(A3:A10000="Asia"),--(MONTH(B3):MONTH(B10000)=10))

but no luck.

Can you help?

"Frank Kabel" wrote:

Hi
first don't replace the '--'. They coerce the boolean values to real
numbers (TRUE=1/FALSE=0)
for your example if you want to get the sum for one specific account
use
=SUMPRODUCT(--(A1:A4000=account_number),--(C1:C4000="collections"),--(D
1:D4000="michigan"),F1:F4000)

Note: this is case sensitive. You may also consider using a pivot table
for this:
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm


--
Regards
Frank Kabel
Frankfurt, Germany


dave wrote:
Frank,

Sorry, I got an error. What do I put in where the "--"
are?

Here is a more accurate example of my worksheet.

Column A lists a range of account numbers so I'd like this
range to find all 4000 accounts.

Column C lists a range of departments so I'd like to
find "collections"

column D lists the location so I want "michigan"

Column F has the balances so I want the sum of every
account that meets these 3 criteria.

Thanks,

Dave
-----Original Message-----
Hi
use SUMPRODUCT instead. e.g.
=SUMPRODUCT(--(A1:A100=100),--(B1:B100="manager"),C1:C100)

--
Regards
Frank Kabel
Frankfurt, Germany


Dave Gremaud wrote:
Does anyone use this function or have any suggestions on a
different function that may work. I have a very large
worksheet with multiple data columns. I am trying to sum
multiple cells by using multiple variables. The SUMIF
function works well but I can't insert more than one
criteria. I have tried many times without success. I
would greatly appreciate any assistance.

Here is an example of my need:

I would like a function to read (if column "a" = 100 and
column "b" = "manager" then sum these fields).

I know there must be a solution but it escapes me.

Thanks!

Dave
.

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

Hi,

You can use

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

Regards

Govind.

Saariko wrote:
Hi again,

I wen to the xLDunamic help page:
Here is another unsuccessful try:
=SUMPRODUCT((A170:A180="Asia")*(MONTH(ROW(B179:B18 0))=9)*(C170:C180))

please?


"Saariko" wrote:


Hi Frank,

I usually use your replies with blind eyes, and Bingo. However, I have tried
this solution of multiple arrays and condition, and a little problem for me
on this one.

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 ?????

Again, I can't use pivot table for this part.

I tried to tweak your funciton:
=SUMPRODUCT(--(A3:A10000="Asia"),--(B3:B10000="9/28/2004"))
=SUMPRODUCT(--(A3:A10000="Asia"),--(MONTH(B3:B10000)=10))
=SUMPRODUCT(--(A3:A10000="Asia"),--(MONTH(B3):MONTH(B10000)=10))

but no luck.

Can you help?

"Frank Kabel" wrote:


Hi
first don't replace the '--'. They coerce the boolean values to real
numbers (TRUE=1/FALSE=0)
for your example if you want to get the sum for one specific account
use
=SUMPRODUCT(--(A1:A4000=account_number),--(C1:C4000="collections"),--(D
1:D4000="michigan"),F1:F4000)

Note: this is case sensitive. You may also consider using a pivot table
for this:
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm


--
Regards
Frank Kabel
Frankfurt, Germany


dave wrote:

Frank,

Sorry, I got an error. What do I put in where the "--"
are?

Here is a more accurate example of my worksheet.

Column A lists a range of account numbers so I'd like this
range to find all 4000 accounts.

Column C lists a range of departments so I'd like to
find "collections"

column D lists the location so I want "michigan"

Column F has the balances so I want the sum of every
account that meets these 3 criteria.

Thanks,

Dave

-----Original Message-----
Hi
use SUMPRODUCT instead. e.g.
=SUMPRODUCT(--(A1:A100=100),--(B1:B100="manager"),C1:C100)

--
Regards
Frank Kabel
Frankfurt, Germany


Dave Gremaud wrote:

Does anyone use this function or have any suggestions on a
different function that may work. I have a very large
worksheet with multiple data columns. I am trying to sum
multiple cells by using multiple variables. The SUMIF
function works well but I can't insert more than one
criteria. I have tried many times without success. I
would greatly appreciate any assistance.

Here is an example of my need:

I would like a function to read (if column "a" = 100 and
column "b" = "manager" then sum these fields).

I know there must be a solution but it escapes me.

Thanks!

Dave

.