SUMIF (Excel '97)
 

I want to sum a particular range using a date range as a criteria,
I have the dates in the worksheet formatted as dd/mm/yyyy.
How do i format this in the criteria?

For example dates for November would only be included anything else would be
ignored.

Thanks,

SUMIF (Excel '97)
 

=sumproduct((month(a2:a22)=11)*b2:b22)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Phendrena" wrote in message
...
I want to sum a particular range using a date range as a criteria,
I have the dates in the worksheet formatted as dd/mm/yyyy.
How do i format this in the criteria?

For example dates for November would only be included anything else would
be
ignored.

Thanks,

SUMIF (Excel '97)
 

=SUMPRODUCT(--(MONTH(A2:A100)=11),--(YEAR(A2:A100)=2007),B2:B100)


"Phendrena" wrote:

I want to sum a particular range using a date range as a criteria,
I have the dates in the worksheet formatted as dd/mm/yyyy.
How do i format this in the criteria?

For example dates for November would only be included anything else would be
ignored.

Thanks,

SUMIF (Excel '97)
 

Thanks for the formula Don Guillet and "Teethless mama", but I need to adjust
it to my worsheet wich the only difference than Phendrena's is that mine has
numbers from B1 to B30 and I need to ADD them ( not SUMPRODUCT) if A1 TO A30
is November 2007. Thanks.
--
Socrates said: I only know, I don''''t know nothing.
I say : I don''''t even know, I don''''t
know nothing.


"Teethless mama" wrote:

=SUMPRODUCT(--(MONTH(A2:A100)=11),--(YEAR(A2:A100)=2007),B2:B100)


"Phendrena" wrote:

I want to sum a particular range using a date range as a criteria,
I have the dates in the worksheet formatted as dd/mm/yyyy.
How do i format this in the criteria?

For example dates for November would only be included anything else would be
ignored.

Thanks,

SUMIF (Excel '97)
 

=SUMPRODUCT(--(MONTH(A1:A30)=11),--(YEAR(A1:A30)=2007),B1:B30)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Learning Excel" wrote in message
...
Thanks for the formula Don Guillet and "Teethless mama", but I need to
adjust
it to my worsheet wich the only difference than Phendrena's is that mine
has
numbers from B1 to B30 and I need to ADD them ( not SUMPRODUCT) if A1 TO
A30
is November 2007. Thanks.
--
Socrates said: I only know, I don''''t know nothing.
I say : I don''''t even know, I don''''t
know nothing.


"Teethless mama" wrote:

=SUMPRODUCT(--(MONTH(A2:A100)=11),--(YEAR(A2:A100)=2007),B2:B100)


"Phendrena" wrote:

I want to sum a particular range using a date range as a criteria,
I have the dates in the worksheet formatted as dd/mm/yyyy.
How do i format this in the criteria?

For example dates for November would only be included anything else
would be
ignored.

Thanks,

SUMIF (Excel '97)
 

Try the formulas presented.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Learning Excel" wrote in message
...
Thanks for the formula Don Guillet and "Teethless mama", but I need to
adjust
it to my worsheet wich the only difference than Phendrena's is that mine
has
numbers from B1 to B30 and I need to ADD them ( not SUMPRODUCT) if A1 TO
A30
is November 2007. Thanks.
--
Socrates said: I only know, I don''''t know nothing.
I say : I don''''t even know, I don''''t
know nothing.


"Teethless mama" wrote:

=SUMPRODUCT(--(MONTH(A2:A100)=11),--(YEAR(A2:A100)=2007),B2:B100)


"Phendrena" wrote:

I want to sum a particular range using a date range as a criteria,
I have the dates in the worksheet formatted as dd/mm/yyyy.
How do i format this in the criteria?

For example dates for November would only be included anything else
would be
ignored.

Thanks,

SUMIF (Excel '97)
 

For some reason, is not working.
To clarify my question:
In B1 to B30 I have values 2,3,5,9,80,95... that I' like the total on the
month of November ( in column A). Thanks

--
Socrates said: I only know, I don''''t know nothing.
I say : I don''''t even know, I don''''t
know nothing.


"Bob Phillips" wrote:

=SUMPRODUCT(--(MONTH(A1:A30)=11),--(YEAR(A1:A30)=2007),B1:B30)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Learning Excel" wrote in message
...
Thanks for the formula Don Guillet and "Teethless mama", but I need to
adjust
it to my worsheet wich the only difference than Phendrena's is that mine
has
numbers from B1 to B30 and I need to ADD them ( not SUMPRODUCT) if A1 TO
A30
is November 2007. Thanks.
--
Socrates said: I only know, I don''''t know nothing.
I say : I don''''t even know, I don''''t
know nothing.


"Teethless mama" wrote:

=SUMPRODUCT(--(MONTH(A2:A100)=11),--(YEAR(A2:A100)=2007),B2:B100)


"Phendrena" wrote:

I want to sum a particular range using a date range as a criteria,
I have the dates in the worksheet formatted as dd/mm/yyyy.
How do i format this in the criteria?

For example dates for November would only be included anything else
would be
ignored.

Thanks,

SUMIF (Excel '97)
 

YES SIR! It worked.
My bad. Good work.
--
Socrates said: I only know, I don''''t know nothing.
I say : I don''''t even know, I don''''t
know nothing.


"Don Guillett" wrote:

Try the formulas presented.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Learning Excel" wrote in message
...
Thanks for the formula Don Guillet and "Teethless mama", but I need to
adjust
it to my worsheet wich the only difference than Phendrena's is that mine
has
numbers from B1 to B30 and I need to ADD them ( not SUMPRODUCT) if A1 TO
A30
is November 2007. Thanks.
--
Socrates said: I only know, I don''''t know nothing.
I say : I don''''t even know, I don''''t
know nothing.


"Teethless mama" wrote:

=SUMPRODUCT(--(MONTH(A2:A100)=11),--(YEAR(A2:A100)=2007),B2:B100)


"Phendrena" wrote:

I want to sum a particular range using a date range as a criteria,
I have the dates in the worksheet formatted as dd/mm/yyyy.
How do i format this in the criteria?

For example dates for November would only be included anything else
would be
ignored.

Thanks,

SUMIF (Excel '97)
 

Glad you got it.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Learning Excel" wrote in message
...
YES SIR! It worked.
My bad. Good work.
--
Socrates said: I only know, I don''''t know nothing.
I say : I don''''t even know, I don''''t
know nothing.


"Don Guillett" wrote:

Try the formulas presented.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Learning Excel" wrote in
message
...
Thanks for the formula Don Guillet and "Teethless mama", but I need to
adjust
it to my worsheet wich the only difference than Phendrena's is that
mine
has
numbers from B1 to B30 and I need to ADD them ( not SUMPRODUCT) if A1
TO
A30
is November 2007. Thanks.
--
Socrates said: I only know, I don''''t know nothing.
I say : I don''''t even know, I don''''t
know nothing.


"Teethless mama" wrote:

=SUMPRODUCT(--(MONTH(A2:A100)=11),--(YEAR(A2:A100)=2007),B2:B100)


"Phendrena" wrote:

I want to sum a particular range using a date range as a criteria,
I have the dates in the worksheet formatted as dd/mm/yyyy.
How do i format this in the criteria?

For example dates for November would only be included anything else
would be
ignored.

Thanks,

SUMIF (Excel '97)
 

thanks for the replies,

So far i'm not having much luck, i have to say i am a novice when it comes
to most things on excel, so i thought i'd help if i gave you the formula to
see if you could suggest where i am going wrong:

=SUMIF('[name.xls]Mid-Term'!$B$3:$B$502,SUMPRODUCT(--(MONTH(B3:B503)=11),--(YEAR(B3:B503)=2007),B3:B503),'[name.xls]Mid-Term'!$G$3:$G$502)

Range: '[name.xls]Mid-Term'!$B$3:$B$502
Criteria: SUMPRODUCT(--(MONTH(B3:B503)=11),--(YEAR(B3:B503)=2007),B3:B503)
Sum Range: '[name.xls]Mid-Term'!$G$3:$G$502

Any further help would be most welcome.





"Teethless mama" wrote:

=SUMPRODUCT(--(MONTH(A2:A100)=11),--(YEAR(A2:A100)=2007),B2:B100)


"Phendrena" wrote:

I want to sum a particular range using a date range as a criteria,
I have the dates in the worksheet formatted as dd/mm/yyyy.
How do i format this in the criteria?

For example dates for November would only be included anything else would be
ignored.

Thanks,

SUMIF (Excel '97)
 

Let me try and show you how the spreadsheet is setup:

Source Data:

Col B ..... ..... Col G
Date ...... .... Reason
Date ...... .... Reason

Date : is formatted as dd/mm/yyyy and the user presses the keyboard shortcut
(CTRL+;) to add in the date.
Reason : is just various text reasons that are picked from a drop-down menu,
all i need to do is just add up if the date if within a specific month
regardless of what text is there as long as there is text.

Summary Data:

Col B ..... ..... ...... Col D
Name 1 .... .... ... .... Data
Name 2 .... .... ... .... Data
Name 3 .... .... ... .... Data

So it's the Col D data field where i am going to pulling the information to
from the source (seperate workbooks).

Hope this helps with my query!!!

thanks,

SUMIF (Excel '97)
 

Look at what you've asked your formula to do.

You have asked it to add the values in column G on your Mid-Term sheet in
name.xls, for the rows where the value in column B on that sheet is equal to
the value of the specfied criterion, which is obtained from by multiplying
column B on your current sheet by two Booleans, one checking that the month
of the date in column B of your current sheet is November, and the other
checking that the year in that column is 2007. [And additionally you've got
unequal ranges, some going to row 502 and some to 503.]

I doubt whether that's what you intended. I guess that the SUMPRODUCT
formula was intended to give your answer, not to be used as the crierion in
the SUMIF formula.
--
David Biddulph

"Phendrena" wrote in message
...
thanks for the replies,

So far i'm not having much luck, i have to say i am a novice when it comes
to most things on excel, so i thought i'd help if i gave you the formula
to
see if you could suggest where i am going wrong:

=SUMIF('[name.xls]Mid-Term'!$B$3:$B$502,SUMPRODUCT(--(MONTH(B3:B503)=11),--(YEAR(B3:B503)=2007),B3:B503),'[name.xls]Mid-Term'!$G$3:$G$502)

Range: '[name.xls]Mid-Term'!$B$3:$B$502
Criteria: SUMPRODUCT(--(MONTH(B3:B503)=11),--(YEAR(B3:B503)=2007),B3:B503)
Sum Range: '[name.xls]Mid-Term'!$G$3:$G$502

Any further help would be most welcome.





"Teethless mama" wrote:

=SUMPRODUCT(--(MONTH(A2:A100)=11),--(YEAR(A2:A100)=2007),B2:B100)


"Phendrena" wrote:

I want to sum a particular range using a date range as a criteria,
I have the dates in the worksheet formatted as dd/mm/yyyy.
How do i format this in the criteria?

For example dates for November would only be included anything else
would be
ignored.

Thanks,