=SUMIF(N2:N154, AND(">1/31/2013","<3/1/2013"), H2:H154)
column N is a list of dates in which an invoice was paid.
column H is a list of amounts for the invoices.
If there is a date listed in column N, that means the invoice was paid.
I am trying to ONLY add the invoice amounts based on a specific date range, so above I am trying to sum up the paid invoice amounts that were paid in the month of February. I have to do this for each month of the year.
How about:
Please Login or Register to view this content.
Would you like to say thanks? Please click the: "Add Reputation" button, on the grey bar below the post.
Hi and welcome to the forum
Try using sumifS() instead of sumif(), it allows for more criteria...
=SUMIF($H$2:$H$154,$N$2:$N$154,">=1/31/2013",$N$2:$N$154,"<=3/1/2013"))
If you still have a problem, i would suggest you upload a sample work book, (all sensitive data removed), showing what data you are working with, a few examples of what your expected outcome is, and how you arrived at that
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"
Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
@FDibbins - When helping with this question, you may want to keep in mind that Excel 2003 does not allow for sumifs. SUMIFS is one of the best formulas I have recently learned, but it is very frustrating to watch it break when older versions of Excel are in use. I know some there is range code that will work around the sumifs issue.
Your example searches results for amounts paid in January, but your explanation requires the sum for February ... ( slight typo I guess, just adapt the solutions proposed with February dates)Originally Posted by pba.stlouis
You can also
1. Use a Pivot Table ( might be overkill)
2, Enterin row 1 and pull down twelve rowsPlease Login or Register to view this content.
@carlajp1 As you can see in the OP's info, he/she is using XL2007, so FD's SUMIFS suggestion was justified and his solution also applies to 2003 ( in case the info is wrong)
Thanks to everyone who has posted! I had tried both the sumifs and also sumif options listed on the posts above. When doing both, I get the same total. but when I manually go in and add up the numbers that should be added up, I don't get the same number. I get a higher number. I checked and double checked again.
Also, each invoice is is paid by a category of options; check, 1099, w2, etc. I summed up each category using sumifs again for the month of February, then summed up all the categories and that total was higher as well. they should be the same.
Here is the formula I used for each category of invoice:
=SUMIFS(H2:H74,I2:I74,data!J2, N2:N74, "<=2/28/2013")
where data!J2 is the cell for "1099"
@ carlajp, I am well aware of which versions of excel sumifS() works in, and if you care to look at the OP's profile, you will see that it says 2007 - hence my suggestionThanks for the heads-up though
@all - No offense was meant. I am new to this site and was not sure if the original poster realized the repercussions of using SUMIFS in their document. I only recently found out the this issue myself. As you can see, I work in 2010 and 2013, but others that view my documents work in older versions of excel, so my formula doesn't work for those individuals.
no offence taken
A belated welcome to the forum
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks