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

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

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