# SumIf or array formula

1. ## SumIf or array formula

So I've searched this forum and all over the place and can't get anything to work...If I had a better understanding of formulas (as I assume some of you do) I think this would be a pretty straight forward and common formula type. Here is the info I have:

Column A: Date
Column E: Part Number
Column H: Cost of Part
Column K: Month References
(all other columns are hidden and not needed)

Range is A2:A5000

I am trying to creat a formula to sum the totals of a given part number for a given month. In other words, I need to get my total costs for a certain part number for each month. I can't use cells as references becuase this worksheet will be used by multiple people and sorting will change, changing the reference cells.

Any help or guidance will be much appreciated!!

Here are a few of the formulas I tried but couldn't quite get to work....

{=SUM((A2:A5000="APR")*(E2:E5000="3001-01-111")*H2:H5000)}

[SUM(IF(\$A\$2:\$A\$5000=\$K2, IF (E\$2:E\$1000="JAN",D\$2:D\$1000),0))]

both formulas gave me zeroes when I should have had numerical results.  Register To Reply

2. ## Re: Need help with a sumif or array formula

If column A contains the month as text then your first formula should work......but if it's a date you might want MONTH function, i.e.

=SUMPRODUCT((MONTH(A2:A5000)=4)*(E2:E5000="3001-01-111"),H2:H5000)  Register To Reply

3. ## Re: Need help with a sumif or array formula

Here is a good read on SumProduct

http://www.xldynamic.com/source/xld.SUMPRODUCT.html  Register To Reply

4. ## Re: Need help with a sumif or array formula

Oh, and the most efficient way in Excel 2007 might be to use SUMIFS function which allows you to sum for multiple conditions, again if date column is the month as text that would be like this

=SUMIFS(H2:H5000,A2:A5000,"APR",E2:E5000,"3001-01-111")

but with true dates in column A you'd need an upper and lower bound for the date column like this:

=SUMIFS(H2:H5000,A2:A5000,">="&DATE(2010,4,1),A2:A5000,"<="&DATE(2010,4,30),E2:E5000,"3001-01-111")  Register To Reply

5. ## Re: SumIf or array formula

Okay I think the month issue is probably what was messing things up! I'll have to give that a try and see where it leads. Thanks for the help!  Register To Reply

6. ## Re: SumIf or array formula

I tried the formulas out and am still getting zeros? I used the formula builder that macs have as an option and came up with one that I think will work best, however, I can't seem to get the dates formatted correctly. SO my question is...how can I format my dates in the formula to correspond with what I have listed (currently listed as date, year, ie Jan-10) or would it be easier to just change all those dates to a text formate such as "JANUARY"??

Here is the formula I've come up with if someone can verify that it should work...I believe the red is my problem area...

=SUMIFS('[GRAPH DATA.xlsx]CRJ200'!\$H\$2:\$H\$5000,'[GRAPH DATA.xlsx]CRJ200'!\$A\$2:\$A\$5000,"(MONTH=1)",'[GRAPH DATA.xlsx]CRJ200'!\$E\$2:  Register To Reply

7. ## Re: SumIf or array formula

MONTH is a function so (MONTH=1) won't work. You need to use the last form (with DATE function) that Daddylonglegs showed with the SUMIFS function, or the MONTH(A2:A5000) form if you're using SUMPRODUCT.  Register To Reply

8. ## Re: SumIf or array formula

So my formula should work if entered as follows?

=SUMIFS('[GRAPH DATA.xlsx]CRJ200'!\$H\$2:\$H\$5000,'[GRAPH DATA.xlsx]CRJ200'!\$A\$2:\$A\$5000,">="&DATE(2010,4,1),A2:A5000,"<="&DATE(2010,4,30),'[GRAPH DATA.xlsx]CRJ200'!\$E\$2:\$E\$5000,15839-2)  Register To Reply

9. ## Re: SumIf or array formula Originally Posted by zachvu So my formula should work if entered as follows?

=SUMIFS('[GRAPH DATA.xlsx]CRJ200'!\$H\$2:\$H\$5000,'[GRAPH DATA.xlsx]CRJ200'!\$A\$2:\$A\$5000,">="&DATE(2010,4,1),A2:A5000,"<="&DATE(2010,4,30),'[GRAPH DATA.xlsx]CRJ200'!\$E\$2:\$E\$5000,15839-2)
I think the second instance of A2:A5000 needs to show the full filename and sheetname as per the first instance.

Note that SUMIFS doesn't work with closed workbooksso GRAPH DATA needs to be open for the formula to work. If it might not be open then you probably need to revert to SUMPRODUCT......  Register To Reply

10. ## Re: SumIf or array formula

I got the formula to work, I just ended up reformatting the date as a text only piece and locked it so nobody else has access to it!

I have another question though... when a part number has a +/- sign in it, how can I use that as an if condition without excel thinking it's an operation? I tried single quotations and parentheses but received errors with both....  Register To Reply

11. ## Re: SumIf or array formula

also... it won't allow me to use anything with a combination of numbers and letters such as 0871BN8  Register To Reply

12. ## Re: SumIf or array formula

Nevermind guys...I figured out I just need to use double quoatations instead of single!!! Thanks!!!  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 