Sum of data based on dates ?

[@Gopigk]

Hi all,

we have a data which will update by employee daily, need to be calculate based on current date. Data need to be like, For the Day / For the month / For the year, should appear automatically based on current date.

Herewith I attached sample file for ready reference.

Request to all, please do the needful

 

[Khalid NGO]

Hi,

There is Emp-1 data only, so you can adjust the SumRange for other Emp accordingly.

B3:
=SUMIFS(Data!G:G,Data!B:B,A1)

B4:
=SUMIFS(Data!G:G,Data!B:B,">"&EOMONTH(A1,-1),Data!B:B,"<="&EOMONTH(A1,0))

B5:
=SUMIFS(Data!G:G,Data!B:B,">="&DATE(YEAR(A1),1,1),Data!B:B,"<="&DATE(YEAR(A1),12,31))

Regards,

 

[@Gopigk]

Dear Khalid Ji,

Thank you very much. It's working. If possible, can you please explain how this formula works. I understood formula for "B3", but unable for "B4 & B5", it's little bit difficult to understand the usage of few arguments like, ">" ,"<", "EOMONTH", "Date(Year)", etc..

Anyway once again thank you very much sir for your wonderful help

 

[Khalid NGO]

Hi again,
Glad it helped

usage of few arguments like, ">" ,"<", "EOMONTH", "Date(Year)", etc..

> and < refers to Greater than and Less than respectively, we need to use these symbols in " " inverted commas in some formulas depend on its nature, for example SUMPRODUCT or IF functions does not require inverted commas.

EOMONTH function is used to get the End Of Month Date. For example your date in cell A1 is 20-Feb-19, then EOMONTH(A1,0) returns 28-Feb-19

DATE(YEAR(A1)
Date functions has 3 parameters, year, month and day.
Month and day used as hard coded as we knew that starting day is 1st January which will remain same. The YEAR(A1) function is used so that when your cell update to new year, it will return the updated year.

Best way to learn is to split each formula and use F9 or Evaluate option.

Hope that helped. Write back if you need further explanation.

Take Care

 

[bosco_yip]

Another formula option,

1] For the Day, B3 :

=SUMIF(Data!B:B,A1,Data!G:G)

2] For the Month, B4 :

=SUMPRODUCT((MONTH(Data!B$3:B$500)=MONTH(A$1))*Data!G$3:G$500)

3] For the Year, B5 :

=SUMPRODUCT((YEAR(Data!B$3:B$500)=YEAR(A$1))*Data!G$3:G$500)

Regards
Bosco

 

[@Gopigk]

Hi again,
Glad it helped



> and < refers to Greater than and Less than respectively, we need to use these symbols in " " inverted commas in some formulas depend on its nature, for example SUMPRODUCT or IF functions does not require inverted commas.

EOMONTH function is used to get the End Of Month Date. For example your date in cell A1 is 20-Feb-19, then EOMONTH(A1,0) returns 28-Feb-19

DATE(YEAR(A1)
Date functions has 3 parameters, year, month and day.
Month and day used as hard coded as we knew that starting day is 1st January which will remain same. The YEAR(A1) function is used so that when your cell update to new year, it will return the updated year.

Best way to learn is to split each formula and use F9 or Evaluate option.

Hope that helped. Write back if you need further explanation.

Take Care

Dear Khalid sir,

Thanks for wonderful explanation, understood very well. Thanks for your support.

 

[@Gopigk]

Another formula option,

1] For the Day, B3 :

=SUMIF(Data!B:B,A1,Data!G:G)

2] For the Month, B4 :

=SUMPRODUCT((MONTH(Data!B$3:B$500)=MONTH(A$1))*Data!G$3:G$500)

3] For the Year, B5 :

=SUMPRODUCT((YEAR(Data!B$3:B$500)=YEAR(A$1))*Data!G$3:G$500)

Regards
Bosco

Dear sir,

It's working, thanks for your kind support. will take little bit time for me to understand usage of Sum product functionality.

thanks once again