Dear all , I have a formula:
Column C is a 'helper' column that contains the month (1= January)
=SUMIFS($B:$B,$C:$C,1,$D:$D,"Maintenance")
The formula will sum column B if the Month is January and column D contains "Maintenance"
But the actual date is contained in Column A and I would like to use this directly in the formula above and remove the helper column
Any help appreciated!
You can use this formula:
=SUMIFS($B:$B,$A:$A,">="&DATE(2016,1,1),$A:$A,"<"&DATE(2016,2,1),$D:$D,"Maintenance")
2016 is the year to be calculated
One way...
=SUMPRODUCT(--(TEXT(A1:A10,"mmm")="Jan"),--(D1:D10="Maintenance"),B1:B10)
You should avoid using entire columns as range references in the SUMPRODUCT function.
Note: empty cells will evaluate as month Jan.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
^ will these formulas work if I extend to another column using Autofill?
In my example the formula will update automatically when I copy and paste at the end of the month (see attachment)
Screen Shot 2016-03-16 at 4.46.58 PM.png
Many members are unable to see images in the *.png format due to forum compatibility issues with some browsers.
If you need to post an image post it in the *.jpg format.
Even better than posting images... post a SMALL sample file. That way we can test solutions directly in the file with the relevant data.
see attachment
You need to normalize your data.
On the INVOICES sheet in column Sub Section you have entries like Garden and Transport/Travel but on the P&L sheet you have those listed as Garden Costs and Transport.
They should be the same on both sheets.
Once you get things normalized, this formula entered in D3:
=SUMPRODUCT(--(TEXT(INVOICES!$B$2:$B$20,"mmmm")=D$2),--(INVOICES!$G$2:$G$20=$C3),INVOICES!$I$2:$I$20)
Copy across to H3 then down as needed.
Ahh Cool!
So the names in Column C on the P&L sheet have to match the data validation labels on the pulldown tabs in INVOICE sheet - shouldnt be a problem!
Thanks
ok, will make a sample file
I would solve this with a pivot table.
See the attached file.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
^ Thx, will check this out
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks