Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extract multiple records matching criteria from list | Excel Worksheet Functions | |||
Multiple Criteria (add or subtract) | Excel Discussion (Misc queries) | |||
Multiple Criteria IF Nesting | Excel Worksheet Functions | |||
Can I get the mode, min, and max with multiple criteria? | Excel Discussion (Misc queries) | |||
Counting "rows", i.e. simultaneous criteria for multiple cells | Excel Worksheet Functions |