INDEX MATCH WITH MULTIPLE (SAME) DATES IN A RANGE

[buckrogers5678]

Hi there,

I'm looking for a good index match function to search for data with in ranges which contain the same data. In this case I'm trying to match dates and the connected values at these dates. However, a date can be present multiple times a month because it's connected to a transaction and these transactions can occur multiple times a day.

Maybe I need to include somekind of sumif() function??

Many thanks if you want to help me out!

 

[Hui]

Buck

Firstly, Welcome to the Chandoo.org Forums

If you want the total for June on Sample 1 use either:
=SUMPRODUCT((M4:M22>I3)*(M4:M22<I4)*(N4:N22))
or
=SUMIFS(N4:N22,M4:M22,">"&I3,M4:M22,"<"&I4)

for multiple dates

I put a date of 1/6/2015 in I3
then use either:
=SUMPRODUCT((M3:M21>=I3)*(M3:M21<=EOMONTH(I3,0))*(N3:N21))
or
=SUMIFS(N4:N22,M4:M22,">="&I3,M4:M22,"<="&EOMONTH(I3,0))

 

[buckrogers5678]

Thank you Hui!!

Next step for me is sample 2 in the same file. In the end I want to overwrite data every month, so I thought to use the index match function as I don't know how many data point I will have in each column. However the single index match function is not suitable for i.e. multiple data entries (same date, multiple times). Is it possible to combine index match with sumifs or sumproduct?

 

[Hui]

I don't understand your requirements

Please highlight them in a sample file

 

[buckrogers5678]

Please find enclosed in the second sheet the highlighted cells. As you can see, the outcome of 1st of June in the index match function is not the same as the data in the detailed table in which multiple transactions at the 1st of june are included.

 

[Hui]

D7: =SUMIFS($N$3:$N$21,$M$3:$M$21,C7)
Copy down

 

[buckrogers5678]

Thank you!