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

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

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,068
Messages
5,545,798
Members
410,708
Latest member
SanTrapGamer
Top