I'm trying to link two different budgets into a third spreadsheet. The final step I'm attempting to accomplish is have an if-and statement reference just the month from a date-formatted cell. One of my departments tracks their budget on a daily basis, i.e. 11/2/2009, 11/3/2009, etc. rather than just 11/2009. I want the if-and statement to return the budget from all days in a given month so i can apply the formula to the whole year, broken down monthly too. I saw a question similar to this one but didn't see any responses so I'm not sure if it can be done. Thanks for the help!
Last edited by Runnit4; 11-11-2009 at 05:55 PM.
Hi,
can you attach a small data sample that illustrates how your workbook is structured? It'll be much easier to suggest a solution. Right now, I'm leaning towards something like SUMPRODUCT or SUMIFS, or an array formula.
Click "Go Advanced" below and then the paper clip icon to attach a file.
cheers
Ok, i have the following formulas entered (in two different columns) to distinguish between 'shipping' and 'receiving' (obv data has been changed). But i need it to also read only the month it occurred:
In H21, =IF(AND(G21="nov",D21="shipping"),F21,"")
In I21, =IF(AND(G21="nov",D21="receiving"),F21,"")
Ok, I'm actually pretty happy i found this, but if anyone reads and is wondering, this is one way of doing what am trying to do...
=TEXT(MONTH(A19)*29,"mmm") to give me jan, feb, mar...
Actually it can be simpler than that....
=TEXT(A19,"mmm")
Hi,
a pivot table would probably be the best way to sum up the data by month and other criteria, like line of business.
Based on the data sample you provided you can use a formula like
=SUMPRODUCT((MONTH(A3:A12=MONTH(H1))*(C3:C12="Shipping")*D3:D12))
where H1 holds a date, to sum up all shipping amounts for a specific month.
hth
That's great, that would be the next step I'm trying to accomplish. Both very helpful, thanks guys!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks