last price between certain dates

oblix

New Member
Joined
Mar 29, 2017
Messages
48
Office Version
  1. 2010
Platform
  1. Windows
What if I want to look up latest price between dates?
Example:
somewhere I have a sheet with from and to date.
from date = a1
to date = a2
This formula I have written looks at all the dates and not between a range of dates I specify. How do I change this formula to include search between these dates?

=IF(G10=0,'PRICE COMPARISSON'!M10,SUMIFS(Table38[[Price]:[Price]],Table38[[Item]:[Item]],Table364894[[RATION ITEM]:[RATION ITEM]],Table38[[Date]:[Date]],G10))

Some one suggested to look at this:

=SUMIFS($B$2:$B$9,$A$2:$A$9,">=" & $D$2, $A$2:$A$9,"<=" & $E$2)

  • The first argument, $B$2:$B$9, is the range with the numbers that we want to sum.
  • The 2nd argument, $A$2:$A$9, is the range to check for criteria 1.
  • The 3rd argument, ">=" & $D$2, is the range with the value for criteria 1 (the Start date), and the operator to use with that value (greater than or equal to)
  • The 4th argument, $A$2:$A$9, is the range to check for criteria 2.
  • The 5th argument, "<=" & $E$2, is the range with the value for criteria 2 (the End date), and the operator to use with that value (less than or equal to)
But how do I incorporate this with my existing formula at top?
Please assist anybody
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

oblix

New Member
Joined
Mar 29, 2017
Messages
48
Office Version
  1. 2010
Platform
  1. Windows
Structured references gets me still confused.
 

oblix

New Member
Joined
Mar 29, 2017
Messages
48
Office Version
  1. 2010
Platform
  1. Windows
Contextures site provided the answer
=IF(G10=0,'PRICE COMPARISSON'!M10,SUMIFS(Table38[[Price]:[Price]],Table38[[Item]:[Item]],Table364894[[RATION ITEM]:[RATION ITEM]],Table38[[Date]:[Date]],">=" & $A$1,Table38[[Date]:[Date]],"<=" & $A$2))
 

Watch MrExcel Video

Forum statistics

Threads
1,114,111
Messages
5,546,005
Members
410,720
Latest member
SSL
Top