Counting & summing formula (until last month,this month,until this month..

**Jhon Mustofa** · 03-12-2014, 11:18 AM

hi all,

i need help how to figure it out, count and sum with criteria by date with month as controller display (drop down list),
- how to showing data/value (counting and summing) with 4 models ----"green area"-----;
1) showing data until last month
2) showing data only this month
3) showing data until this month
4) total data until last month, total this month and until this month

when i click month (in cell K2) drop down list data in display....

for example;
when i click a month March (in cell K2), ;
- counting & summing data until last month (January, February)
- counting & summing only this month (March)
- total counting & summing data until this month (January + February + March)

note; not use Pivot

please, see my workbook....

thanks!!

**etaf** · 03-12-2014, 12:40 PM

i would use

For upto the end of last month
=COUNTIFS(B:B,F4,A:A,">="&1/1/13,A:A,"<="&EOMONTH($K$2,-1))

=SUMIFS(C:C,B:B,F4,A:A,">="&1/1/13,A:A,"<="&EOMONTH($K$2,-1))

but that means changing the dropdown to be an actual date - see attached

Just this month
=COUNTIFS(B:B,F4,A:A,">="&$K$2,A:A,"<="&EOMONTH($K$2,0))

=SUMIFS(C:C,B:B,F4,A:A,">="&$K$2,A:A,"<="&EOMONTH($K$2,0))

Until this month

=COUNTIFS(B:B,F4,A:A,">="&1/1/13,A:A,"<="&EOMONTH($K$2,0))

=SUMIFS(C:C,B:B,F4,A:A,">="&1/1/13,A:A,"<="&EOMONTH($K$2,0))

I hope thats what you need - check , as i have not checked extensively

i have used 1/1/13 as the starting date - but you can change that to however, far you need to go back

**Jhon Mustofa** · 03-12-2014, 12:57 PM

etaf, FANTASTIC....

THANK YOU SO MUCH, I AM GLAD NOW.....

**Jhon Mustofa** · 03-12-2014, 01:03 PM

I AM MISSING IT, WHAT GRAND TOTAL FORMULA ( COUNTING & SUMMING)??

once again, thanks etaf....

**etaf** · 03-12-2014, 01:05 PM

for the Grand Total - you would just do a SUM()

If you put the criteria
PUPN
TAX
BUMN
AUCTION

on every row - then you could just copy the formulas down
otherwise you need to adjust to pickup the cell with that criteria in

thanks for the rep

**Jhon Mustofa** · 03-12-2014, 01:14 PM

hi etaf, thank for your suggestion...

Counting & summing formula (until last month,this month,until this month..

LinkBack

Thread Tools

Rate This Thread

Display

Counting & summing formula (until last month,this month,until this month..

Re: Counting & summing formula (until last month,this month,until this month..

Re: Counting & summing formula (until last month,this month,until this month..

Re: Counting & summing formula (until last month,this month,until this month..

Re: Counting & summing formula (until last month,this month,until this month..

Re: Counting & summing formula (until last month,this month,until this month..

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Auto instert Month names for This month, Last month and Next month

Formula for average duration for each month based on the ending month

[SOLVED] Date formula with text in same cell but using previous month not current month

[SOLVED] Summing cumulative daily values for each month then resetting next month

[SOLVED] Excel 2007 : Counting date in specific month by month

Bookmarks

Bookmarks

Posting Permissions