Excel 2007 : Total for the current month

**gregwestwood** · 06-20-2012, 05:21 AM

Hi,

I have a cell that gives the total for the current day with the formula

=SUMIF(A2:A10014,TODAY(),D2:D10014)

In the A column is the date format 08/06/2012. It works pretty good and always displays the current daily total. I have been looking however to have the cell next to it giving me the total for the whole of the current month, i.e. anything in colums A with the current month's value of June.

.....Col A....................Col D
08/06/2012..................20
09/06/2012..................52
09/06/2012..................18
12/06/2012..................10

So I want a formula that will tell me the month total of 100 from the above.

Sorry for my long winded explanation. I've never posted in a forum before.

Greg

**RobertMika** · 06-20-2012, 05:33 AM

=sumproduct(--(month($a$3:$a$6)=6)*($d$3:$d$6))

or
=sumproduct(--(month($a$3:$a$6)=(month(e1)))*($d$3:$d$6))

where E1 is 1/6/12

**gregwestwood** · 06-20-2012, 05:51 AM

That's fine but I think that =6 refers to the month of June... I want this to update to the current month automatically.

=SUMIF(A2:A10014,">="&TODAY()-DAY(TODAY())+1,D2:D10014)-SUMIF(A2:A10014,">="&DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)*D2:D10014)

Did it using this, thanks for your help though. :-)

**RobertMika** · 06-20-2012, 07:37 AM

If you need sum for month of the current month
=SUMPRODUCT(--(MONTH(A2:A11)=MONTH(TODAY()))*(D2:D11))

**gregwestwood** · 06-21-2012, 08:16 AM

Originally Posted by dewilk

If you need sum for month of the current month
=SUMPRODUCT(--(MONTH(A2:A11)=MONTH(TODAY()))*(D2:D11))

That is excellent! Works a treat. May I be patience testing and ask how come there is -- after the first bracket?

But it works and I'm using it now! Very cool. Thanks very much.

**Cutter** · 06-21-2012, 09:35 AM

The -- is called a double unary and it converts TRUE/FALSE values to their numeric 1/0 equivalents.

You could replace that SUMPRODUCT() formula with a simpler SUMIF()

=SUMIF(A:A,">"&EOMONTH(TODAY(),-1),D:D)
which adds up all values for dates that are after the last day of the previous month
assuming you have no values for dates later than the current month