| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Use SUMIF, VLOOKUP, HLOOKUP, LOOKUP, AND ??
Hi,
I'm facing a problem in using excel worksheet functions. I need to sum up all number that fall on Dec and with colA of 1001. meaning i need to add up B2+C2+D2+B7+C7+D7. I use the following formula but can't get it. Anyone care to share with me which part I did wrong. =SUM(IF(AND(B1:F1="Dec",A2:A11=1001),B2:F11,0)) which return #Value!. I understand that if i use SUM(IF(, I have to press CTRL + SHIFT + Enter and this return me with 0. Can I know where I went wrong and how should I add the fill up so that I can achieve B2+C2+D2+B7+C7+D7 = 16?? A B C D E F 1 29Dec08 30Dec08 31Dec08 01Jan09 02Jan09 2 1001 1 7 1 1 1 3 1002 2 2 2 2 2 4 1003 1 1 1 3 1 5 1004 6 1 1 1 1 6 1005 1 1 1 1 1 7 1001 3 2 2 1 4 8 1002 1 1 1 1 1 9 1003 1 1 1 1 1 10 1004 1 1 2 1 1 11 1005 1 1 1 2 1 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Use SUMIF, VLOOKUP, HLOOKUP, LOOKUP, AND ??
Realigned!
------------------ Hi, I'm facing a problem in using excel worksheet functions. I need to sum up all number that fall on Dec and with colA of 1001. meaning i need to add up B2+C2+D2+B7+C7+D7. I use the following formula but can't get it. Anyone care to share with me which part I did wrong. =SUM(IF(AND(B1:F1="Dec",A2:A11=1001),B2:F11,0)) which return #Value!. I understand that if i use SUM(IF(, I have to press CTRL + SHIFT + Enter and this return me with 0. Can I know where I went wrong and how should I add the fill up so that I can achieve B2+C2+D2+B7+C7+D7 = 16?? A B C D E F 1 29Dec08 30Dec08 31Dec08 01Jan09 02Jan09 2 1001 1 7 1 1 1 3 1002 2 2 2 2 2 4 1003 1 1 1 3 1 5 1004 6 1 1 1 1 6 1005 1 1 1 1 1 7 1001 3 2 2 1 4 8 1002 1 1 1 1 1 9 1003 1 1 1 1 1 10 1004 1 1 2 1 1 11 1005 1 1 1 2 1 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Use SUMIF, VLOOKUP, HLOOKUP, LOOKUP, AND ??
Try
=SUMPRODUCT((A2:A20=1001)*(MONTH(B1:F1)=12)*(B2:F2 0)) Mike "z060081" wrote: Realigned! ------------------ Hi, I'm facing a problem in using excel worksheet functions. I need to sum up all number that fall on Dec and with colA of 1001. meaning i need to add up B2+C2+D2+B7+C7+D7. I use the following formula but can't get it. Anyone care to share with me which part I did wrong. =SUM(IF(AND(B1:F1="Dec",A2:A11=1001),B2:F11,0)) which return #Value!. I understand that if i use SUM(IF(, I have to press CTRL + SHIFT + Enter and this return me with 0. Can I know where I went wrong and how should I add the fill up so that I can achieve B2+C2+D2+B7+C7+D7 = 16?? A B C D E F 1 29Dec08 30Dec08 31Dec08 01Jan09 02Jan09 2 1001 1 7 1 1 1 3 1002 2 2 2 2 2 4 1003 1 1 1 3 1 5 1004 6 1 1 1 1 6 1005 1 1 1 1 1 7 1001 3 2 2 1 4 8 1002 1 1 1 1 1 9 1003 1 1 1 1 1 10 1004 1 1 2 1 1 11 1005 1 1 1 2 1 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Use SUMIF, VLOOKUP, HLOOKUP, LOOKUP, AND ??
z060081 wrote:
Realigned! ------------------ Hi, I'm facing a problem in using excel worksheet functions. I need to sum up all number that fall on Dec and with colA of 1001. meaning i need to add up B2+C2+D2+B7+C7+D7. I use the following formula but can't get it. Anyone care to share with me which part I did wrong. =SUM(IF(AND(B1:F1="Dec",A2:A11=1001),B2:F11,0)) which return #Value!. I understand that if i use SUM(IF(, I have to press CTRL + SHIFT + Enter and this return me with 0. Can I know where I went wrong and how should I add the fill up so that I can achieve B2+C2+D2+B7+C7+D7 = 16?? A B C D E F 1 29Dec08 30Dec08 31Dec08 01Jan09 02Jan09 2 1001 1 7 1 1 1 3 1002 2 2 2 2 2 4 1003 1 1 1 3 1 5 1004 6 1 1 1 1 6 1005 1 1 1 1 1 7 1001 3 2 2 1 4 8 1002 1 1 1 1 1 9 1003 1 1 1 1 1 10 1004 1 1 2 1 1 11 1005 1 1 1 2 1 Your first test inside the AND should be: MONTH(B1:F1)=12 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Use SUMIF, VLOOKUP, HLOOKUP, LOOKUP, AND ??
None of the above but use SUMPRODUCT
=SUMPRODUCT((A2:A11=1001)*(MONTH(B1:F1)=12)*B2:F11 ) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "z060081" <u49337@uwe wrote in message news:913483fa13abd@uwe... Hi, I'm facing a problem in using excel worksheet functions. I need to sum up all number that fall on Dec and with colA of 1001. meaning i need to add up B2+C2+D2+B7+C7+D7. I use the following formula but can't get it. Anyone care to share with me which part I did wrong. =SUM(IF(AND(B1:F1="Dec",A2:A11=1001),B2:F11,0)) which return #Value!. I understand that if i use SUM(IF(, I have to press CTRL + SHIFT + Enter and this return me with 0. Can I know where I went wrong and how should I add the fill up so that I can achieve B2+C2+D2+B7+C7+D7 = 16?? A B C D E F 1 29Dec08 30Dec08 31Dec08 01Jan09 02Jan09 2 1001 1 7 1 1 1 3 1002 2 2 2 2 2 4 1003 1 1 1 3 1 5 1004 6 1 1 1 1 6 1005 1 1 1 1 1 7 1001 3 2 2 1 4 8 1002 1 1 1 1 1 9 1003 1 1 1 1 1 10 1004 1 1 2 1 1 11 1005 1 1 1 2 1 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Use SUMIF, VLOOKUP, HLOOKUP, LOOKUP, AND ??
Are those proper dates in row 1 (formatted to look like that) or are
they text dates ? What does one look like when viewed in the formula bar? Pete On Feb 4, 1:42*pm, "z060081" <u49337@uwe wrote: Realigned! ------------------ Hi, I'm facing a problem in using excel worksheet functions. I need to sum up all number that fall on Dec and with colA of 1001. meaning i need to add up B2+C2+D2+B7+C7+D7. I use the following formula but can't get it. Anyone care to share with me which part I did wrong. =SUM(IF(AND(B1:F1="Dec",A2:A11=1001),B2:F11,0)) which return #Value!. I understand that if i use SUM(IF(, I have to press CTRL + SHIFT + Enter and this return me with 0. Can I know where I went wrong and how should I add the fill up so that I can achieve B2+C2+D2+B7+C7+D7 = 16?? * * * *A * * * * * *B * * * * * *C * * * * * * *D * * * * * E * * * *F * * * * * 1 * * * * * * * 29Dec08 *30Dec08 *31Dec08 *01Jan09 *02Jan09 2 * *1001 * * * * *1 * * * * * *7 * * * * * * *1 * * * * * * 1 * * * * 1 3 * *1002 * * * * *2 * * * * * *2 * * * * * * *2 * * * * * * 2 * * * * 2 4 * *1003 * * * * *1 * * * * * *1 * * * * * * *1 * * * * * * 3 * * * * 1 5 * *1004 * * * * *6 * * * * * *1 * * * * * * *1 * * * * * * 1 * * * * 1 6 * *1005 * * * * *1 * * * * * *1 * * * * * * *1 * * * * * * 1 * * * * 1 7 * *1001 * * * * *3 * * * * * *2 * * * * * * *2 * * * * * * 1 * * * * 4 8 * *1002 * * * * *1 * * * * * *1 * * * * * * *1 * * * * * * 1 * * * * 1 9 * *1003 * * * * *1 * * * * * *1 * * * * * * *1 * * * * * * 1 * * * * 1 10 *1004 * * * * *1 * * * * * *1 * * * * * * *2 * * * * * * 1 * * * * 1 11 *1005 * * * * *1 * * * * * *1 * * * * * * *1 * * * * * * 2 * * * * 1- Hide quoted text - - Show quoted text - |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Use SUMIF, VLOOKUP, HLOOKUP, LOOKUP, AND ??
Glenn wrote:
z060081 wrote: Realigned! ------------------ Hi, I'm facing a problem in using excel worksheet functions. I need to sum up all number that fall on Dec and with colA of 1001. meaning i need to add up B2+C2+D2+B7+C7+D7. I use the following formula but can't get it. Anyone care to share with me which part I did wrong. =SUM(IF(AND(B1:F1="Dec",A2:A11=1001),B2:F11,0)) which return #Value!. I understand that if i use SUM(IF(, I have to press CTRL + SHIFT + Enter and this return me with 0. Can I know where I went wrong and how should I add the fill up so that I can achieve B2+C2+D2+B7+C7+D7 = 16?? A B C D E F 1 29Dec08 30Dec08 31Dec08 01Jan09 02Jan09 2 1001 1 7 1 1 1 3 1002 2 2 2 2 2 4 1003 1 1 1 3 1 5 1004 6 1 1 1 1 6 1005 1 1 1 1 1 7 1001 3 2 2 1 4 8 1002 1 1 1 1 1 9 1003 1 1 1 1 1 10 1004 1 1 2 1 1 11 1005 1 1 1 2 1 Your first test inside the AND should be: MONTH(B1:F1)=12 And as pointed out by others, you should use SUMPRODUCT(). |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Use SUMIF, VLOOKUP, HLOOKUP, LOOKUP, AND ??
Hi Bernard Liengme,
Really thanks a lot for the help... Appreciate a lot for the help.. Regards, Bernard Liengme wrote: None of the above but use SUMPRODUCT =SUMPRODUCT((A2:A11=1001)*(MONTH(B1:F1)=12)*B2:F1 1) best wishes Hi, [quoted text clipped - 38 lines] 11 1005 1 1 1 2 1 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| lookup combine vlookup with hlookup | Excel Worksheet Functions | |||
| HLOOKUP, VLOOKUP, LOOKUP??? | Excel Worksheet Functions | |||
| Help with Hlookup/Vlookup and Lookup. From multipul sheets | Excel Worksheet Functions | |||
| how can I use an hlookup in a sumif? | Excel Worksheet Functions | |||
| I've tried Sumproduct, SumIf, Vlookup and Hlookup. | Excel Worksheet Functions | |||
Linear Mode
