SUMIF with dates... help

[Jazzer]

Hi All,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I have been using the following sumif<o></o>
<o></o>
=SUMIF('NOMAD DATA'!E11:E390,"<="&D10,'NOMAD DATA'!I11:I390)<o></o>
<o></o>
Cell D10 = the desired date on which we wish to know the result<o></o>
‘NOMAD DATA’ Column E = various dates<o></o>
‘NOMAD DATA’ Column I = the amounts we are looking to sum<o></o>
<o></o>
This has been working fine<o></o>
<o></o>
However I now have another variant to contend with ‘NOMAD DATA’ Column F contains dates in some of the rows<o></o>
I now need to SUM ‘NOMAD DATA’ Column I – excluding any amounts which have a date in column F before or equal to the date in cell D10 <o></o>
<o></o>
<o></o>
<o></o>
Thanks<o></o>
James<o></o>
<o></o>
<o></o>

 

[Dannyh1]

Hi Try,

=SUMPRODUCT(('NOMAD DATA'!E11:E390<=D10)*('NOMAD DATA'!F11:F390>D10)*I11:I390)

 

[Jazzer]

Hi Danny

thanks for that.

it seems to contain an error though - cant see it myself but its not getting any result.

James

 

[T. Valko]

Hi All,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I have been using the following sumif<o></o>
<o></o>
=SUMIF('NOMAD DATA'!E11:E390,"<="&D10,'NOMAD DATA'!I11:I390)<o></o>
<o></o>
Cell D10 = the desired date on which we wish to know the result<o></o>
‘NOMAD DATA’ Column E = various dates<o></o>
‘NOMAD DATA’ Column I = the amounts we are looking to sum<o></o>
<o></o>
This has been working fine<o></o>
<o></o>
However I now have another variant to contend with ‘NOMAD DATA’ Column F contains dates in some of the rows<o></o>
I now need to SUM ‘NOMAD DATA’ Column I – excluding any amounts which have a date in column F before or equal to the date in cell D10 <o></o>
<o></o>
<o></o>
<o></o>
Thanks<o></o>
James<o></o>
<o></o>
<o></o>

Do you want this as a new separate formula or do you want to add this condition to the already existing formula?

What version of Excel are you using?

 

[Jazzer]

Do you want this as a new separate formula or do you want to add this condition to the already existing formula?

What version of Excel are you using?

adding this condition to the already existing formula would be ideal

excel 2007

thanks

 

[T. Valko]

Hi All,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I have been using the following sumif<o></o>
<o></o>
=SUMIF('NOMAD DATA'!E11:E390,"<="&D10,'NOMAD DATA'!I11:I390)<o></o>
<o></o>
Cell D10 = the desired date on which we wish to know the result<o></o>
‘NOMAD DATA’ Column E = various dates<o></o>
‘NOMAD DATA’ Column I = the amounts we are looking to sum<o></o>
<o></o>
This has been working fine<o></o>
<o></o>
However I now have another variant to contend with ‘NOMAD DATA’ Column F contains dates in some of the rows<o></o>
I now need to SUM ‘NOMAD DATA’ Column I – excluding any amounts which have a date in column F before or equal to the date in cell D10 <o></o>
<o></o>
<o></o>
<o></o>
Thanks<o></o>
James<o></o>
<o></o>
<o></o>

adding this condition to the already existing formula would be ideal

excel 2007

thanks

OK, excluding amounts when the date is <=whatever is the same as including the amounts when the date is >whatever.

So, try this...

=SUMIFS('NOMAD DATA'!I11:I390,'NOMAD DATA'!E11:E390,"<="&D10,'NOMAD DATA'!F11:F390,">"&D10)

 

[Jazzer]

Perfect

thanks

 

[Jazzer]

Perfect

thanks

One last thing... is it possible to add the INDIRECT function to this formula?
the "NOMADDATA" tab is deleted and refreshed which will produce ref errors on the current formula...

Many Thanks

 

[T. Valko]

One last thing... is it possible to add the INDIRECT function to this formula?
the "NOMADDATA" tab is deleted and refreshed which will produce ref errors on the current formula...

Many Thanks

Try this...

=IFERROR(SUMIFS(INDIRECT("'NOMAD DATA'!I11:I390"),INDIRECT("'NOMAD DATA'!E11:E390"),"<="&D10,INDIRECT("'NOMAD DATA'!F11:F390"),">"&D10),"")

 

[Jazzer]

thanks - that's done the trick on that front.

I'm not convinced that my sum is correct.
I think my explanation of what was actually needed was unclear

the sum should do the following

SUM NOMADDATA colomn I when NOMADDATA colomn E is "<=" to D10 but not if NOMADDATA colomn F is "<=" to D10 (only when the first past of the query is matched though)