| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| count month when date is in day/month/year format | Excel Worksheet Functions | |||
| Tell me which "season" (Month/Day through Month/Day) a date(Month/Day/Year) falls in (any year)??? | Excel Discussion (Misc queries) | |||
| Sort month/date/year data using month and date only | Excel Discussion (Misc queries) | |||
| trying to get day/month/year froamt while user enters year only | New Users to Excel | |||
| details of month | Excel Worksheet Functions | |||
Linear Mode
