How can I use the LOOKUP formulas with dates and not numbers?

[lunelmai]

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

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

 

[lunelmai]

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]

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

 

[Teethless mama]

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]

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!

 

[Rick Rothstein]

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

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

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

 

[lunelmai]

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]

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.

 

[lunelmai]

Rick,

My bad, it works!

Perfect, thank you so much!!