Sum data based on month

[AC5FF]

I have something like a checkbook register created in Excel. I need to have a total of a column (say Column I) where the date purchased will fall within a billing cycle.

Hmmm; okay, column I has everything ever purchased. But, I want a billing cycle total block to only total column I from the 19th to the 18th of the following month; and that formula will need to be automatic; not something to edit every month.

I was trying to use the SumIf or SumIfs function. But not getting it to work right. Not sure why...
I know I could do that in Access, but I'm not 100% sure how to accomplish it in Excel. Any ideas?

Is it possible in Excel to use "IF(X=Y and A>b, 1, 0) ??

 

[Brianwarnock]

You have not said
1 Where your dates are
2 If the number of rows is fixed

If 2 is not true then you are going to need to write a function, if they are fixed and for this example assume col J for the dates then I believe that a SUMPRODUCT will work such as

=SUMPRODUCT((I1:I4)*(J1:J4>=DATE(YEAR(TODAY()),MONTH(TODAY()),19))*(J1:J4<=DATE(YEAR(TODAY()),MONTH(TODAY())+1,18)))

Note it is not an "array" formula just hit enter.

Brian

 

[AC5FF]

I racked my brain back n forth for quite a while after posting here and just kept coming up blank.

You might be on the right track Brian, but I don't think that sumproduct would be a good solution. Lemme see if I can try and clarify my spreadsheet:

Col D: For Who purchased (1 of 4 selectable)
Col E: Total of Purchase
Col F: Date of Purchase
Col G: Invoice Date
Col H: Cleared Date

I've got a couple of fields at the top of the spreadsheet that keep totals for who items were purchased for (Based on Col D). That was a bugger to get to work, but I've got it now...
I need to show how much was purchased during the current billing cycle. So, I will need to sum the amounts in col E based on Col F; when Today() is between the 18th of Last month and the 19th of this month; OR when Today() is between the 19th of this month and the 18th of next month (if Today()>18th of this month)....
(Whew! I'm lost typing it!! LOL No wonder I can't figure it out! LOL)

I think that makes more sense than my first post...
Thx

 

[shades]

Howdy. Sounds like maybe a lookup table for dates might be a starting point; makes life much simpler to expand.

 

[Brianwarnock]

when Today() is between the 18th of Last month and the 19th of this month; OR when Today() is between the 19th of this month and the 18th of next month (if Today()>18th of this month)....

That's either 2 totals, 2 sumproducts, or 1 total with a date range from 18th of last month to 18th of next month, in either case just a tweak of the Date part of the sumproduct.
What don't you like about Sumproduct.?

Note that the criteria in Sumproduct can use Cell references, unlike Sumif, and might have provided a simple solution to totals for who.

Brian

EDit realised that I had made a mistake see below

 

[namliam]

Why not re-allign the dates? i.e. Somedate - 18 in this case.
This would ' force ' the dates to revert back to a 'normal' month in which case the rest is "easy"-er

 

[Brianwarnock]

OK what you are saying is the billing period is dependent upon todays date.
What I would do is create 2 cells and give them names say stdate and edate and use these in the Sumproduct. They can be on a separate worksheet

sdate
=IF(DAY(TODAY())<19,DATE(YEAR(TODAY()),MONTH(TODAY())-1,19),DATE(YEAR(TODAY()),MONTH(TODAY()),19))

edate
=IF(DAY(TODAY())>18,DATE(YEAR(TODAY()),MONTH(TODAY())+1,18),DATE(YEAR(TODAY()),MONTH(TODAY()),18))

Sumproduct now assuming data only in rows1-4
=SUMPRODUCT((e1:e4)*(f1:f4>=stdate)*(f1:f4<=edate))

I notice that column I has become column e

Brian

 

[Bilbo_Baggins_Esq]

hmmm, perhaps I'm missing something here, but I am curious why this "Array Formula" {CTRL+SHIFT+ENTER} wouldn't work:

{=SUM(IF(dates>=BeginDate,values,0))-SUM(IF(dates>EndDate,values,0))}

I tested this on a simple sample sheet and it worked fine.
I tried to use the "AND" & "OR" inside the IF() so as to perform the sum only on the matched dates, but it wants to sum all values inside the array.

 

[Brianwarnock]

It will work, but I don't see why you would prefer it to sumproduct, assuming you named the arrays in sumproduct, which incidently I would do, then


=SUMPRODUCT(values*(dates>=stdate)*(dates<=edate))

The start and end dates need calculating separately in both approaches.

Brian

 

[AC5FF]

Brian

This worked! Thank You, but I was just slapped with another "IF" .... Grrrrr
Employees.. whata ya do!

Here's the actual formula I have:

SUMPRODUCT((E10:E100)*(F10:F100>=C4)*(F10:F100<=E4) )

Now he wants to base this on if the purchase has cleared or not (Y or "" entered in Col H). If there is an entry in Col H, than DON"T add that sum from Col E.

BTW: C4 is where I put sDATE and E4 is where I put eDATE

Thx!

 

[Brianwarnock]

Just add another test *(h10:h100="Y")

SUMPRODUCT((E10:E100)*(F10:F100>=C4)*(F10:F100<=E4)*(h10:h100<>"Y"))

Brian

 

[AC5FF]

That easy? WoW
I was reading too much into the 'sumproduct' line. I didn't think your original code would work because it 'looked' and 'sounded' like you are multiplying the 3(4) columns. I was surprised when it did work! I need to read up on this 'sumproduct' some more!

 

[qafself]

Hi,

This is one of the best guides around

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[Brianwarnock]

ED
That is not a guide, its a bible.

All Excel users should make friends with sumproduct, The MS help only scratches at its surface, I'm not surprised after all they have dropped help on Datedif, is that still supported in 2007?

For those not familiar with Sumproduct it does as AC55FF says multiplies the relevent cells in the arrays together then sums the results, but if the array is used as criteria (a1:a111="FRED") then for each cell containing FRED it returns 1 and for a False result returns 0, so you end up with eg across 1 values and 4 criteria

12*1*1*0*0 =0
24*1*1*1*1 =24 etc

This is a simple example.

Brian

 

[qafself]

Couldn't agree more, Brian - SUMPRODUCT is one of the most useful and versatile functions I've come across.

I suppose that as 2007 has SUMIFS, that is the way ahead - personally, I will be staying with 2003

 

[AC5FF]

I could have really used "SUMPRODUCT" years ago when I was running a 'database' out of Excel (couldn't get it to work in Access at that time. I was Access ignorant).

I looked up that link Ed. Really helped explain what was going on. Thank You. I got it bookmarked and will probably visit it frequently!

I tried the SUMIFS, but again, couldnt' get it to work... I may have mis-understood how to write the function; maybe that link will help?

 

[qafself]

You have to have EXCEL 2007 for SUMIFS - it allows multiple criteria and so does away with the need to use SUMPRODUCT - the XLDYNAMIC link is only for sumproduct

I don''t have 2007 so can't advise further

 

[AC5FF]

i'm running 2007, and sumproduct does work.
I just haven't figured out the sumifs yet...