Multiple Criteria Sumif/Sum..tried & failed Ctrl+Shift+Enter,
 

Strange problem this...I have followed to the letter differnt methods of
solving this:

3 Columns A, B and C

Column A is in date format DD/MM/YYYY at the moment 17/06/2005
Column B is picked from a list but for now contains just text (i.e-
name of saleman say "Bob")
Column C is currency format and for now just contains just one entry
("£55.00")

What i want is to sum all sales from A for that date if the saleman is
Bob

And this is what i did......

=SUM((B1:B11="bob")*(A1:A11="17/06/2005")*C1:C11)

Remembering of course to press Ctrl+Shift+Enter to get the squiggly
lines for the array.

Nothing. The value i get is "0"

I'm sure i'm missing soomething obvoius here but can't see it.

Anyone able to help????

Thanks,

Chris


--
chris100
------------------------------------------------------------------------
chris100's Profile: http://www.excelforum.com/member.php...o&userid=25166
View this thread: http://www.excelforum.com/showthread...hreadid=386956

I've just found this works if you change the date to a number format and
use the corresponding number in the formula. THis column could then be
hidden next to a column with the correct date in it.

I would still be interested in any solutions other than this.

P.s - on general Multiple criteria stuff i found this link useful:

http://www.j-walk.com/ss/excel/tips/tip74.htm


--
chris100
------------------------------------------------------------------------
chris100's Profile: http://www.excelforum.com/member.php...o&userid=25166
View this thread: http://www.excelforum.com/showthread...hreadid=386956

Chris,

This might work. Enter the date and name (for which you want to find the
total sales) in D1 and D2 respectively, and enter the following formula in
D3. (These three could be any three cells of your choice, but modify the
formula accordingly. Now you can dynamically enter the date and name, and
obtain the corresponding total sales information.

=SUMPRODUCT(--(A1:A11=D1),--(B1:B11=D2),(C1:C11))

Regards,
B. R. Ramachandran

"chris100" wrote:


Strange problem this...I have followed to the letter differnt methods of
solving this:

3 Columns A, B and C

Column A is in date format DD/MM/YYYY at the moment 17/06/2005
Column B is picked from a list but for now contains just text (i.e-
name of saleman say "Bob")
Column C is currency format and for now just contains just one entry
("£55.00")

What i want is to sum all sales from A for that date if the saleman is
Bob

And this is what i did......

=SUM((B1:B11="bob")*(A1:A11="17/06/2005")*C1:C11)

Remembering of course to press Ctrl+Shift+Enter to get the squiggly
lines for the array.

Nothing. The value i get is "0"

I'm sure i'm missing soomething obvoius here but can't see it.

Anyone able to help????

Thanks,

Chris


--
chris100
------------------------------------------------------------------------
chris100's Profile: http://www.excelforum.com/member.php...o&userid=25166
View this thread: http://www.excelforum.com/showthread...hreadid=386956