SUMIF problem, would love some help please

[dannybsaintsfc]

Hi everyone, i have had a look around the forum but cant find the help i need. I am trying to set up, what i imagine for most of you, is a simple spreadsheet.

Screen Shot 2012-10-12 at 22.36.43.jpg

At the lower left (B34) you can see that i would like to get the sum of (E3 to E30) but only if the text in I3 to I30 says "SYSW"
i would like a similar formula in (B35) for "YSW" and so on.
i am not a very competent excel user and understand there should be a formula using SUMIF but i don't know if its because i have merged cells in the E column or what (i would also like to merge the I column to make it look nicer where there are less staff in the session if i could.

any help would be greatly appreciated - thanks in advance.
Dan

[FDibbins]

should i upload a picture of the solution, or would you rather upload a sample of your workbook lol?

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[dannybsaintsfc]

thanks a lot - here is the spreadsheet lol, i'm not new honest!

[FDibbins]

this will sum your values based on the criteria you specify...

=SUMIF($I$2:$I$30,RIGHT(A32,LEN(A32)-12),$E$2:$E$30)

HOWEVER!!!! the fact that you have used "merge and center" means that this will not work. M&C messes up all sorts of formulas, and we try to avoid using it. I un=merged all the cells in E and used =cell-above to "copy" the values down.

try the above and let me know if that is something you can work with

[dannybsaintsfc]

That's amazing, thank you very, very much! i wonder if you could try and explain the formula a little for me if you get 5 mins as it doesn't make any sense to me at all (i cant even see reference to the text?) The internet was invented for and is so much better due to people like you, thanks again for sharing your skills so freely!

[FDibbins]

sure, i would be happy to explain it for you

=SUMIF($I$2:$I$30,RIGHT(A32,LEN(A32)-12),$E$2:$E$30)

the sumif() functions works by looking into a range, and then based on a specified criteria in that range, adds all values that match the criteria

in your case the criteria is actually buried inside other text "Total hours SYSW". but the length of what you are looking for varies, so i couldnt just take the last 4 characters. so what i did was find the length (LEN) of the text string and subtracted 12 from that (Total hours ) to calc how many characters are needed. this was then fed into the right() function to give the criteria

LEN(A32)-12)--->len(Total hours SYSW)-12 = 4
RIGHT(A32,4)--->right(total hours SYSW) = SYSW
=SUMIF($I$2:$I$30,SYSW,$E$2:$E$30)----> your regular sumif()

I know you said you wanted to keep your "merge and center" but that plays havoc with this sort of function. take for instance the 3.25 in E7. the SYSW in I7 matches with that, but the YSW in I8 has no corresponding value in E8 (because there is actually no E8 anymore - the M&C makes E7:E10 all become E7)

i could probably come up with a convoluted formula to work around that, but it would be easier if you could remove the M&C and add the 3.25 to E8:E10. this can be done with a simple =cell-above, and to keep it looking semi-neat, format the color to white so it doesnt show.

if you have a problem doing this, give me a shout and i will help you with it.

and thanks for the kind words and the rep, we all always appreciate them

[dannybsaintsfc]

i really appreciate you spending more time explaining this formula, but i have to say that i still dont really have a clue what you did, but i am glad you did!
i don't really use excel very much, but you have really saved me a massive job, thanks again