Solved

Excel SUMIF for specific dates.

Posted on 2013-12-13
11
710 Views
Last Modified: 2013-12-13
I have 2 columns of data. Dates and currency.
I need a formula that will look for a specific month and year  in the date range and then sum up corresponding currency values.

The issue i have is that the date is not plain text, it comes from another formula. The same goes for currency on one of my sheets. I have a few sheets for apply this to.

If I replace the date as actual date text I can make this work with SUMIF but when the date comes from another formula my end result is always $0.00

How to do I specify a range of dates and have it pass the date values instead of the formula that generated those dates?
I attached an example of what I want it to look like.
I'd like each formula to reference the year 2014 in the corner and the month JAN.... above.
The purpose of my report is to go through a list of all the support contracts we have and add up the ammounts for each month of the year.

Thanks
excel-dates.png
0
Comment
Question by:baysysadmin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
11 Comments
 
LVL 11

Accepted Solution

by:
Angelp1ay earned 500 total points
ID: 39717243
Check attachment :)

=SUMIFS($I$12:$I$15,$B$12:$B$15,">="&B2,$B$12:$B$15,"<="&EOMONTH(B2,0))

Open in new window

SumIfsWithDates.xlsx
0
 

Author Comment

by:baysysadmin
ID: 39717291
Great start but your formula has the same issue as mine.
It only works if the date is text. But mine comes form another formula which looks like this..

=IF($B12="",".",INDEX(Network!$P$2:$P$1000,MATCH(CONCATENATE("*",$B12,"*"),Network!$A$2:$A$1000,0)))

and returns a value of

20-Jan-14

 So how do we pass that value as date text?
0
 
LVL 4

Expert Comment

by:LCCSAM
ID: 39717297
Maybe record a macro to copy the formula results into a separate list of values then work from there? (or is that infeasible?)
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:baysysadmin
ID: 39717307
This sheet will be used by few people, im trying to keep it as simple as possible with as few extra columns as possible.

I already have a separate summary list of these date which is made by the big formula above.
Id hate to make another list from my list.

Is there no way to change how it passes that date value?
0
 

Author Comment

by:baysysadmin
ID: 39717391
Im not sure why but I got it working.
I changed the date range to a Named range and now number values add up.
I guess it doesnt like $I$12:$I$15 way of doing it.

ill do some more testing before closing the question.


Thanks for your help.
0
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 39717422
All of my cells are dates. Sorry should have pointed out even the month headers are now dates (with custom formatting).
0
 

Author Comment

by:baysysadmin
ID: 39717508
Yes i saw that and it worked for me with named ranges.


I am not having another issue.
We use Atlassian confluence, and after I embedd the excel all my numbers show as $0.00
I think confluence excel plug in cant render SUMIFS formulas.
Ive had this issue with other formulas and conditional formating.

Is there a different way of doing this, using some more legacy formulas?
0
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 39717552
You could add a column to your second table that gives the first of that month.
=EOMONTH(B20,-1)+1

Open in new window


Then you could use SUMIF instead of SUMIFS (because you just match with equal instead of in the range).
0
 

Author Comment

by:baysysadmin
ID: 39717648
didnt work, so I tried it this way and it worked and embeded fine with confluence BUT it didnt work for those dates that came from a formula only text dates, even after using named ranges.


{=SUM(IF(YEAR(SoftwareDates)=YEAR(B$2),IF(MONTH(SoftwareDates)=MONTH(B$2),SoftwarePrices,0)))}

Turns out that some of the forumals in my date range return a "."
The reason i put a . is so I can see that there is a fromula there rather than having a blank cell.
so I changed the . to a 0 and now dates evalute fine using above formula.
0
 

Author Closing Comment

by:baysysadmin
ID: 39717698
Thanks
0
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 39717805
Glad you found a way!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question