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

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

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

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

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

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 -

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().

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