# 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.

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)

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

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")

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!

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:

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.

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)

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......

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....

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

12. ## Re: SumIf or array formula

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

##### Users Browsing this Thread

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1