Total count update as per date criteria

[Nitesh Kumar]

Hi Champ,


i need total count in sheet1 as per date from sheet2 total count.

PFA for better understand.

 

[Jnegi]

Hi Nitesh,

Use the below formula in your sheet & I have also attached the excel for your reference.

Array Formula: SUM((Sheet2!$A$4:$A$24<=Answer!A2)*(Sheet2!$B$4:$B$24))
Sumif: =SUMIF(Sheet2!$A$4:$A$24,"<="&Answer!A2,Sheet2!$B$4:$B$24)

Hope its meeting your requirement.

Thanks

 

[Eloise T]

See attachment. Does this do what you want?

 

[PP3321]

@Jnegi
SUMPRODUCT works as well?

=SUMPRODUCT((Sheet2!$A$4:$A$24<=Answer!A2)*(Sheet2!$B$4:$B$24))

 

[Nitesh Kumar]

it's not like that, i want total count in front of date in sheet 1, the date match with last date of sheet2. example is here (if you calculate in sheet 2 date from 05/09/2016 to 06/13/2016 is 55, same 55 count showing in front of 06/13/2016 in sheet 1), same with next date if you calculate in sheet 2 date from 05/09/2016 to 06/14/2016 is 64, same 64 count showing in front of 06/14/2016 in sheet 1.

 

[bosco_yip]

it's not like that, i want total count in front of date in sheet 1, the date match with last date of sheet2. example is here (if you calculate in sheet 2 date from 05/09/2016 to 06/13/2016 is 55, same 55 count showing in front of 06/13/2016 in sheet 1), same with next date if you calculate in sheet 2 date from 05/09/2016 to 06/14/2016 is 64, same 64 count showing in front of 06/14/2016 in sheet 1.

Maybe,

In Answer sheet B2, copy down :

=IF(A2<=LOOKUP(9.9E+307,Sheet2!A:A),SUMIF(Sheet2!$A:A,"<="&A2,Sheet2!B:B),"")

Regards
Bosco

 

[Nitesh Kumar]

thanks bosco !!!
you did, what i am looking.