Hi everyone,

I hope someone will be kind enough to help me...

this is the data I have:

A B C D E F

1 56 1/19/08 12.0 14.8 23
2 98 2/24/08 58.2 21.8 12
3 73 3/12/08 88.2 78.1 08
4 24 9/11/08 78 21.3 85

I have about 900 entries in my sheet, and I need to pull the numbers from
the "E" column that are associated with a certain month (January to December)
that appears in the "C" column. Ultimately, I will need to add the data that
I found for each month, to come up with a total/month.

I've been trying to use some lookup functions, but none of worked out well
so far.

Does anyone have any suggestions??

Thank you!

Try this formula...

=SUMPRODUCT((MONTH(C1:C100)=3)*(E1:E100))

Change the upper range row number (the 100 in my example formula) to the
largest row number you expect to have data in. Note that I hard coded the
month number (3 in my example formula), but you could use a cell reference
instead (that is, say, K5 in place of the 3 and put your month number in
that cell).

--
Rick (MVP - Excel)


"lunelmai" wrote in message
...
Hi everyone,

I hope someone will be kind enough to help me...

this is the data I have:

A B C D E F

1 56 1/19/08 12.0 14.8 23
2 98 2/24/08 58.2 21.8 12
3 73 3/12/08 88.2 78.1 08
4 24 9/11/08 78 21.3 85

I have about 900 entries in my sheet, and I need to pull the numbers from
the "E" column that are associated with a certain month (January to
December)
that appears in the "C" column. Ultimately, I will need to add the data
that
I found for each month, to come up with a total/month.

I've been trying to use some lookup functions, but none of worked out well
so far.

Does anyone have any suggestions??

Thank you!

Hi Rick,

Thank you very much for getting back to me so quickly.
I tried your formula, but it gives me a VALUE error...I'm using Excel 03 by
the way, I don't know if that changes anything.



"Rick Rothstein" wrote:

Try this formula...

=SUMPRODUCT((MONTH(C1:C100)=3)*(E1:E100))

Change the upper range row number (the 100 in my example formula) to the
largest row number you expect to have data in. Note that I hard coded the
month number (3 in my example formula), but you could use a cell reference
instead (that is, say, K5 in place of the 3 and put your month number in
that cell).

--
Rick (MVP - Excel)


"lunelmai" wrote in message
...
Hi everyone,

I hope someone will be kind enough to help me...

this is the data I have:

A B C D E F

1 56 1/19/08 12.0 14.8 23
2 98 2/24/08 58.2 21.8 12
3 73 3/12/08 88.2 78.1 08
4 24 9/11/08 78 21.3 85

I have about 900 entries in my sheet, and I need to pull the numbers from
the "E" column that are associated with a certain month (January to
December)
that appears in the "C" column. Ultimately, I will need to add the data
that
I found for each month, to come up with a total/month.

I've been trying to use some lookup functions, but none of worked out well
so far.

Does anyone have any suggestions??

Thank you!

I'm using XL2003 also. I'm not sure what to tell you as I tested the formula
before posting it and it worked on my system. Okay, maybe I have an idea. Is
your first row a header row? If so, change the formula to this...

=SUMPRODUCT((MONTH(C2:C100)=3)*(E2:E100))

--
Rick (MVP - Excel)


"lunelmai" wrote in message
...
Hi Rick,

Thank you very much for getting back to me so quickly.
I tried your formula, but it gives me a VALUE error...I'm using Excel 03
by
the way, I don't know if that changes anything.



"Rick Rothstein" wrote:

Try this formula...

=SUMPRODUCT((MONTH(C1:C100)=3)*(E1:E100))

Change the upper range row number (the 100 in my example formula) to the
largest row number you expect to have data in. Note that I hard coded the
month number (3 in my example formula), but you could use a cell
reference
instead (that is, say, K5 in place of the 3 and put your month number in
that cell).

--
Rick (MVP - Excel)


"lunelmai" wrote in message
...
Hi everyone,

I hope someone will be kind enough to help me...

this is the data I have:

A B C D E F

1 56 1/19/08 12.0 14.8 23
2 98 2/24/08 58.2 21.8 12
3 73 3/12/08 88.2 78.1 08
4 24 9/11/08 78 21.3 85

I have about 900 entries in my sheet, and I need to pull the numbers
from
the "E" column that are associated with a certain month (January to
December)
that appears in the "C" column. Ultimately, I will need to add the
data
that
I found for each month, to come up with a total/month.

I've been trying to use some lookup functions, but none of worked out
well
so far.

Does anyone have any suggestions??

Thank you!

In column E you may have a text number, or space

this formula will ignore text value

=SUMPRODUCT(--(MONTH(C1:C4)=3),E1:E4)



lunelmai" wrote:

Hi Rick,

Thank you very much for getting back to me so quickly.
I tried your formula, but it gives me a VALUE error...I'm using Excel 03 by
the way, I don't know if that changes anything.



"Rick Rothstein" wrote:

Try this formula...

=SUMPRODUCT((MONTH(C1:C100)=3)*(E1:E100))

Change the upper range row number (the 100 in my example formula) to the
largest row number you expect to have data in. Note that I hard coded the
month number (3 in my example formula), but you could use a cell reference
instead (that is, say, K5 in place of the 3 and put your month number in
that cell).

--
Rick (MVP - Excel)


"lunelmai" wrote in message
...
Hi everyone,

I hope someone will be kind enough to help me...

this is the data I have:

A B C D E F

1 56 1/19/08 12.0 14.8 23
2 98 2/24/08 58.2 21.8 12
3 73 3/12/08 88.2 78.1 08
4 24 9/11/08 78 21.3 85

I have about 900 entries in my sheet, and I need to pull the numbers from
the "E" column that are associated with a certain month (January to
December)
that appears in the "C" column. Ultimately, I will need to add the data
that
I found for each month, to come up with a total/month.

I've been trying to use some lookup functions, but none of worked out well
so far.

Does anyone have any suggestions??

Thank you!

Guys,

Thank you for your help. I tried both formulas, and they still don't work. I
think I know what the problem is though.
My table is not only composed of numbers, it also have cells with text. I
forgot to include it in my previous sample. I basically have a list of
transactions for each of my clients, therefore, column "C" sometimes have
cells with text before going back to numbers.
I would need some formula that would not include text values at all...
Because I did try to use the formulas you gave me for just 10 cells with
numbers and they work perfectly...

Please let me know if there is such a formula that can just ignore the cells
with text!

Thanks again for your help!


"Teethless mama" wrote:

In column E you may have a text number, or space

this formula will ignore text value

=SUMPRODUCT(--(MONTH(C1:C4)=3),E1:E4)



lunelmai" wrote:

Hi Rick,

Thank you very much for getting back to me so quickly.
I tried your formula, but it gives me a VALUE error...I'm using Excel 03 by
the way, I don't know if that changes anything.



"Rick Rothstein" wrote:

Try this formula...

=SUMPRODUCT((MONTH(C1:C100)=3)*(E1:E100))

Change the upper range row number (the 100 in my example formula) to the
largest row number you expect to have data in. Note that I hard coded the
month number (3 in my example formula), but you could use a cell reference
instead (that is, say, K5 in place of the 3 and put your month number in
that cell).

--
Rick (MVP - Excel)


"lunelmai" wrote in message
...
Hi everyone,

I hope someone will be kind enough to help me...

this is the data I have:

A B C D E F

1 56 1/19/08 12.0 14.8 23
2 98 2/24/08 58.2 21.8 12
3 73 3/12/08 88.2 78.1 08
4 24 9/11/08 78 21.3 85

I have about 900 entries in my sheet, and I need to pull the numbers from
the "E" column that are associated with a certain month (January to
December)
that appears in the "C" column. Ultimately, I will need to add the data
that
I found for each month, to come up with a total/month.

I've been trying to use some lookup functions, but none of worked out well
so far.

Does anyone have any suggestions??

Thank you!

See if this array-entered** formula works for you then...

=SUM(IF(ISNUMBER(C2:C100),(MONTH(C2:C100)=3)*(E2:E 100),""))

**Commit this formula using Ctrl+Shift+Enter, not just Enter by itself.

--
Rick (MVP - Excel)


"lunelmai" wrote in message
...
Guys,

Thank you for your help. I tried both formulas, and they still don't work.
I
think I know what the problem is though.
My table is not only composed of numbers, it also have cells with text. I
forgot to include it in my previous sample. I basically have a list of
transactions for each of my clients, therefore, column "C" sometimes have
cells with text before going back to numbers.
I would need some formula that would not include text values at all...
Because I did try to use the formulas you gave me for just 10 cells with
numbers and they work perfectly...

Please let me know if there is such a formula that can just ignore the
cells
with text!

Thanks again for your help!


"Teethless mama" wrote:

In column E you may have a text number, or space

this formula will ignore text value

=SUMPRODUCT(--(MONTH(C1:C4)=3),E1:E4)



lunelmai" wrote:

Hi Rick,

Thank you very much for getting back to me so quickly.
I tried your formula, but it gives me a VALUE error...I'm using Excel
03 by
the way, I don't know if that changes anything.



"Rick Rothstein" wrote:

Try this formula...

=SUMPRODUCT((MONTH(C1:C100)=3)*(E1:E100))

Change the upper range row number (the 100 in my example formula) to
the
largest row number you expect to have data in. Note that I hard coded
the
month number (3 in my example formula), but you could use a cell
reference
instead (that is, say, K5 in place of the 3 and put your month number
in
that cell).

--
Rick (MVP - Excel)


"lunelmai" wrote in message
...
Hi everyone,

I hope someone will be kind enough to help me...

this is the data I have:

A B C D E
F

1 56 1/19/08 12.0 14.8 23
2 98 2/24/08 58.2 21.8 12
3 73 3/12/08 88.2 78.1 08
4 24 9/11/08 78 21.3 85

I have about 900 entries in my sheet, and I need to pull the
numbers from
the "E" column that are associated with a certain month (January to
December)
that appears in the "C" column. Ultimately, I will need to add the
data
that
I found for each month, to come up with a total/month.

I've been trying to use some lookup functions, but none of worked
out well
so far.

Does anyone have any suggestions??

Thank you!

hey Rick,
for some reasons when I use your formula (with Ctrl+Shift+Enter), it gave me
the following error message:
"Array formulas are not valid in merged cells"
I'm not exactly sure why...(sorry, i'm far from being an Excel expert, and I
greatly appreciate your help)

"Rick Rothstein" wrote:

See if this array-entered** formula works for you then...

=SUM(IF(ISNUMBER(C2:C100),(MONTH(C2:C100)=3)*(E2:E 100),""))

**Commit this formula using Ctrl+Shift+Enter, not just Enter by itself.

--
Rick (MVP - Excel)


"lunelmai" wrote in message
...
Guys,

Thank you for your help. I tried both formulas, and they still don't work.
I
think I know what the problem is though.
My table is not only composed of numbers, it also have cells with text. I
forgot to include it in my previous sample. I basically have a list of
transactions for each of my clients, therefore, column "C" sometimes have
cells with text before going back to numbers.
I would need some formula that would not include text values at all...
Because I did try to use the formulas you gave me for just 10 cells with
numbers and they work perfectly...

Please let me know if there is such a formula that can just ignore the
cells
with text!

Thanks again for your help!


"Teethless mama" wrote:

In column E you may have a text number, or space

this formula will ignore text value

=SUMPRODUCT(--(MONTH(C1:C4)=3),E1:E4)



lunelmai" wrote:

Hi Rick,

Thank you very much for getting back to me so quickly.
I tried your formula, but it gives me a VALUE error...I'm using Excel
03 by
the way, I don't know if that changes anything.



"Rick Rothstein" wrote:

Try this formula...

=SUMPRODUCT((MONTH(C1:C100)=3)*(E1:E100))

Change the upper range row number (the 100 in my example formula) to
the
largest row number you expect to have data in. Note that I hard coded
the
month number (3 in my example formula), but you could use a cell
reference
instead (that is, say, K5 in place of the 3 and put your month number
in
that cell).

--
Rick (MVP - Excel)


"lunelmai" wrote in message
...
Hi everyone,

I hope someone will be kind enough to help me...

this is the data I have:

A B C D E
F

1 56 1/19/08 12.0 14.8 23
2 98 2/24/08 58.2 21.8 12
3 73 3/12/08 88.2 78.1 08
4 24 9/11/08 78 21.3 85

I have about 900 entries in my sheet, and I need to pull the
numbers from
the "E" column that are associated with a certain month (January to
December)
that appears in the "C" column. Ultimately, I will need to add the
data
that
I found for each month, to come up with a total/month.

I've been trying to use some lookup functions, but none of worked
out well
so far.

Does anyone have any suggestions??

Thank you!

The merged cells are what is causing your problems... they always cause
problems. For future questions, always mention if merged cells are involved.
Now, for your problem.... we need to know what is merged (I'm guessing some
of the cells in Column C?). Show us some examples of what is in your merged
cells and what cells you merged to produce it.

--
Rick (MVP - Excel)


"lunelmai" wrote in message
...
hey Rick,
for some reasons when I use your formula (with Ctrl+Shift+Enter), it gave
me
the following error message:
"Array formulas are not valid in merged cells"
I'm not exactly sure why...(sorry, i'm far from being an Excel expert, and
I
greatly appreciate your help)

"Rick Rothstein" wrote:

See if this array-entered** formula works for you then...

=SUM(IF(ISNUMBER(C2:C100),(MONTH(C2:C100)=3)*(E2:E 100),""))

**Commit this formula using Ctrl+Shift+Enter, not just Enter by itself.

--
Rick (MVP - Excel)


"lunelmai" wrote in message
...
Guys,

Thank you for your help. I tried both formulas, and they still don't
work.
I
think I know what the problem is though.
My table is not only composed of numbers, it also have cells with text.
I
forgot to include it in my previous sample. I basically have a list of
transactions for each of my clients, therefore, column "C" sometimes
have
cells with text before going back to numbers.
I would need some formula that would not include text values at all...
Because I did try to use the formulas you gave me for just 10 cells
with
numbers and they work perfectly...

Please let me know if there is such a formula that can just ignore the
cells
with text!

Thanks again for your help!


"Teethless mama" wrote:

In column E you may have a text number, or space

this formula will ignore text value

=SUMPRODUCT(--(MONTH(C1:C4)=3),E1:E4)



lunelmai" wrote:

Hi Rick,

Thank you very much for getting back to me so quickly.
I tried your formula, but it gives me a VALUE error...I'm using
Excel
03 by
the way, I don't know if that changes anything.



"Rick Rothstein" wrote:

Try this formula...

=SUMPRODUCT((MONTH(C1:C100)=3)*(E1:E100))

Change the upper range row number (the 100 in my example formula)
to
the
largest row number you expect to have data in. Note that I hard
coded
the
month number (3 in my example formula), but you could use a cell
reference
instead (that is, say, K5 in place of the 3 and put your month
number
in
that cell).

--
Rick (MVP - Excel)


"lunelmai" wrote in message
...
Hi everyone,

I hope someone will be kind enough to help me...

this is the data I have:

A B C D E
F

1 56 1/19/08 12.0 14.8 23
2 98 2/24/08 58.2 21.8 12
3 73 3/12/08 88.2 78.1 08
4 24 9/11/08 78 21.3 85

I have about 900 entries in my sheet, and I need to pull the
numbers from
the "E" column that are associated with a certain month (January
to
December)
that appears in the "C" column. Ultimately, I will need to add
the
data
that
I found for each month, to come up with a total/month.

I've been trying to use some lookup functions, but none of
worked
out well
so far.

Does anyone have any suggestions??

Thank you!

Rick,

My bad, it works!

Perfect, thank you so much!!

"lunelmai" wrote:

hey Rick,
for some reasons when I use your formula (with Ctrl+Shift+Enter), it gave me
the following error message:
"Array formulas are not valid in merged cells"
I'm not exactly sure why...(sorry, i'm far from being an Excel expert, and I
greatly appreciate your help)

"Rick Rothstein" wrote:

See if this array-entered** formula works for you then...

=SUM(IF(ISNUMBER(C2:C100),(MONTH(C2:C100)=3)*(E2:E 100),""))

**Commit this formula using Ctrl+Shift+Enter, not just Enter by itself.

--
Rick (MVP - Excel)


"lunelmai" wrote in message
...
Guys,

Thank you for your help. I tried both formulas, and they still don't work.
I
think I know what the problem is though.
My table is not only composed of numbers, it also have cells with text. I
forgot to include it in my previous sample. I basically have a list of
transactions for each of my clients, therefore, column "C" sometimes have
cells with text before going back to numbers.
I would need some formula that would not include text values at all...
Because I did try to use the formulas you gave me for just 10 cells with
numbers and they work perfectly...

Please let me know if there is such a formula that can just ignore the
cells
with text!

Thanks again for your help!


"Teethless mama" wrote:

In column E you may have a text number, or space

this formula will ignore text value

=SUMPRODUCT(--(MONTH(C1:C4)=3),E1:E4)



lunelmai" wrote:

Hi Rick,

Thank you very much for getting back to me so quickly.
I tried your formula, but it gives me a VALUE error...I'm using Excel
03 by
the way, I don't know if that changes anything.



"Rick Rothstein" wrote:

Try this formula...

=SUMPRODUCT((MONTH(C1:C100)=3)*(E1:E100))

Change the upper range row number (the 100 in my example formula) to
the
largest row number you expect to have data in. Note that I hard coded
the
month number (3 in my example formula), but you could use a cell
reference
instead (that is, say, K5 in place of the 3 and put your month number
in
that cell).

--
Rick (MVP - Excel)


"lunelmai" wrote in message
...
Hi everyone,

I hope someone will be kind enough to help me...

this is the data I have:

A B C D E
F

1 56 1/19/08 12.0 14.8 23
2 98 2/24/08 58.2 21.8 12
3 73 3/12/08 88.2 78.1 08
4 24 9/11/08 78 21.3 85

I have about 900 entries in my sheet, and I need to pull the
numbers from
the "E" column that are associated with a certain month (January to
December)
that appears in the "C" column. Ultimately, I will need to add the
data
that
I found for each month, to come up with a total/month.

I've been trying to use some lookup functions, but none of worked
out well
so far.

Does anyone have any suggestions??

Thank you!