Added details only if it is within a certain month of the year
 

Hello, I am struggling with a formula that i wondered if anyone could asssit
me with?
I am trying to get a calulation that only adds up all the figures in coloumn
J (column only contains numbers) for the 10th month of 2009 (date is in
column D). I have tried a few times but haven't had any joy.
I would appreciate any assistance and guidance on this.

What i have tried:
=SUM(IF((--(TEXT('Air Imports'!D5:D10000,"mmyyyy")="102009"))

=COUNTIF('Air Imports'!J5:J10000,IF('Air
Imports'!D5:D10000,"mmyyyy")="102009")

--
Jim

Added details only if it is within a certain month of the year
 

Try this:

=SUMPRODUCT(--(TEXT('Air Imports'!D5:D10000,"mmyyyy")="102009"),'Air
Imports'!J5:J10000)

Hope this helps.

Pete

On Dec 23, 10:51*am, Jim wrote:
Hello, I am struggling with a formula that i wondered if anyone could asssit
me with?
I am trying to get a calulation that only adds up all the figures in coloumn
J (column only contains numbers) for the 10th month of 2009 (date is in
column D). I have tried a few times but haven't had any joy.
I would appreciate any assistance and guidance on this.

What i have tried:
=SUM(IF((--(TEXT('Air Imports'!D5:D10000,"mmyyyy")="102009"))

=COUNTIF('Air Imports'!J5:J10000,IF('Air
Imports'!D5:D10000,"mmyyyy")="102009")

--
Jim

Added details only if it is within a certain month of the year
 

=SUMIF('Air Imports'!D5:D10000,"20/10/2009",'Air Imports'!J5:J10000)

OR

=SUMPRODUCT(('Air Imports'!D5:D10000=DATE(2009,10,20))*('Air
Imports'!J5:J10000))

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Jim" wrote:

Hello, I am struggling with a formula that i wondered if anyone could asssit
me with?
I am trying to get a calulation that only adds up all the figures in coloumn
J (column only contains numbers) for the 10th month of 2009 (date is in
column D). I have tried a few times but haven't had any joy.
I would appreciate any assistance and guidance on this.

What i have tried:
=SUM(IF((--(TEXT('Air Imports'!D5:D10000,"mmyyyy")="102009"))

=COUNTIF('Air Imports'!J5:J10000,IF('Air
Imports'!D5:D10000,"mmyyyy")="102009")

--
Jim

Added details only if it is within a certain month of the year
 

Thanks for looking at my question Ms-Exl, for the formula's below would they
not only pull through the information for the 20th Oct? It is for any cell
for that month that i was looking for, so i have tried using a * in place of
the 20 but that does not seem to using all of the cells that this applies to
but does use a lot of them?
Would there be any reason that it is leaving some cells out?
Any idea?
Thanks again

--
Jim


"Ms-Exl-Learner" wrote:

=SUMIF('Air Imports'!D5:D10000,"20/10/2009",'Air Imports'!J5:J10000)

OR

=SUMPRODUCT(('Air Imports'!D5:D10000=DATE(2009,10,20))*('Air
Imports'!J5:J10000))

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Jim" wrote:

Hello, I am struggling with a formula that i wondered if anyone could asssit
me with?
I am trying to get a calulation that only adds up all the figures in coloumn
J (column only contains numbers) for the 10th month of 2009 (date is in
column D). I have tried a few times but haven't had any joy.
I would appreciate any assistance and guidance on this.

What i have tried:
=SUM(IF((--(TEXT('Air Imports'!D5:D10000,"mmyyyy")="102009"))

=COUNTIF('Air Imports'!J5:J10000,IF('Air
Imports'!D5:D10000,"mmyyyy")="102009")

--
Jim

Added details only if it is within a certain month of the year
 

Try this...

=SUMPRODUCT(('Air Imports'!D5:D10000=DATE(2009,10,1))*('Air
Imports'!D5:D10000<=DATE(2009,10,31))*('Air Imports'!J5:J10000))

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Jim" wrote:

Thanks for looking at my question Ms-Exl, for the formula's below would they
not only pull through the information for the 20th Oct? It is for any cell
for that month that i was looking for, so i have tried using a * in place of
the 20 but that does not seem to using all of the cells that this applies to
but does use a lot of them?
Would there be any reason that it is leaving some cells out?
Any idea?
Thanks again

--
Jim


"Ms-Exl-Learner" wrote:

=SUMIF('Air Imports'!D5:D10000,"20/10/2009",'Air Imports'!J5:J10000)

OR

=SUMPRODUCT(('Air Imports'!D5:D10000=DATE(2009,10,20))*('Air
Imports'!J5:J10000))

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Jim" wrote:

Hello, I am struggling with a formula that i wondered if anyone could asssit
me with?
I am trying to get a calulation that only adds up all the figures in coloumn
J (column only contains numbers) for the 10th month of 2009 (date is in
column D). I have tried a few times but haven't had any joy.
I would appreciate any assistance and guidance on this.

What i have tried:
=SUM(IF((--(TEXT('Air Imports'!D5:D10000,"mmyyyy")="102009"))

=COUNTIF('Air Imports'!J5:J10000,IF('Air
Imports'!D5:D10000,"mmyyyy")="102009")

--
Jim