Using AutoSum Clarification

First time user, long time reader here. I hope this is the right place to ask my question.

I have created a spread sheet that adds my columns and rows, however, I then created a new row using the names I have for which I want totals for each name instead of manually entering the numbers. I'm confused on how my formula (or macro?) should be. In other words, column B has different names, column C has their hours (I have numerous columns like this). I then created new rows for each name and want the hours added. lol I think I'm sounding redundent here...help! Thanks in advance.

Hi, it may be easier to post a sample workbook with before/after results. I have no doubt your problem will be solved quicker this way

Thanks Hurricanefly, and again my ignorance shows...but how do I attach a sample doc?

Hi, click reply then go advanve. use the paperclip upload file. Then submit and your done

Sorry for sounding daft here! So in column B for example you have GVN then in column C hours. So you want a formula that only sums GVN & column C hours? if so try this in cell C18

Please Login or Register  to view this content.

If that is not what you are looking for I'm a little confused as it sound so simple and im sure it is

Not all of column C's hours, just what belongs to each individual. And you aren't the daft one here, it's more than likely me I'm going to try your formula in case you are right and I'm just being the idiot...

Hold on, column B will have different names in? the ones at the bottom? If so the formula will work just needs a little adjusting. I just assumed to sum GVN as that is the only name in column B?

I guess I'm looking for a formula that will search GVN, or J Ferg so that each time a new line is added for them with their hours, my totals at the bottom for each individual will change. I have many names listed, not just the sample I created. Does that make more sense? I truly apologize for my lack of knowledge, as it's been many, many years since I've used Excel...

One more thing: if you look at my post with my attachment, it explains that although one name is in a row, there are other names in columns. That's why I was looking for more of a search method to the names so that when hours are changed or added, my totals at the bottom change with it.

Hi, it's not you it's me who is getting confused. I'm getting confused with the names at the bottom? I can give you a formula to sum J.F GVN & G.H with the hours. But it wouldn't work with J.BAILY as this name would not be in the formula. The totals would change when you add a row and hours. Sorry if this feels like i'm not helping I just want to make sure you get the correct solution.

With you, okay i'm almost there, just one last question. Column B & Column C go together D & E. But which Column does I go with as in hours? Is it I with M or I with P? Then I can workout what Column L corresponds with.

L & M go together and O & P go together. They may not have names yet, but most likely will. You rock for having patience with me lol. I'm an ol' dog learning new tricks...seems Excel was easier years ago...

O....M....G!!! By George you got it!!! I'm kissing my screen right now! This is exactly what I needed. I even did a test and added GVN to one of the hours and it calculated it. YOU ROCK HURRICANEFLY! Can't thank you enough! For some reason in my head I was thinking of some type of search so my hours at the bottom would total. Wow such a relief! I'm checking out your formula's, that must have taken a bit to do! Hurricanefly, thank you for making my day, and for being SO patient and quick. You're the best (Here's a sidebar question, I need to delete the spreadsheet from here, how do I do that?)

No probs i'm glad it worked for you. I have never deleted a sample from here the best thing to do is ask a moderator or admin. They can be found on the home page at the bottom. You can sleep easy tonight lol

I will on both counts! lol Thanks again for everything. I added to your user reputation

Yes, I'm seeing the numbers are too much. I wanted the total hours to match the hours columns, so I'm just shortening the formula...didn't panic...yet! lol That's why I deleted my comment and said needed to revise question. I'm going to keep this thread open for a bit....just in case. Thanks for responding Hurricanefly.

Hi, sorry here is the revised results there should be no problems here, but let me know if there are

All done and deleted. Did you still want to total TBD?.

YOU are just the BEST! And no, I figured it out. As soon as I started typing in the formula, I knew what it was about and how it was being done. Again, can't thank you enough for all your help. I think now I can offically close this thread Have a good one Hurricanefly!

In column J you select just the completes. In column G (sorry my bad) are where you can seperate the 72.48s and the 50.59s. And glad you won't kill me lol.

Okay so you want 72.48 screening & 72.48 Applicability & 72.48 & 72.48 Evaluation & in row 69 there is a 72.48 screen. All of these or an individual one? Sorry for the questions just don't want to post wrong answer too you

I know what you want it is no problem to do ( He says) there is one problem, I have been playing around and the problem is where there are 2 or 3 names in one cell the formula is not picking those up. Can these be put in to their own cells?

Not names, in some cells in column G there are 72.48 Screening & 50.59 Screening A & 50.59 Screening B. What I mean is the Sum/Count function wont pick up the 72.48 Screening as it also has 50.59 Screening. Does that make sense? or will they all be in individual cells. I hope I have explained that okay?

Ok, we discussed and decided it would be best to not seperate what is inside certain cells. So! What I need now is complete vs. non completes = % can that be done?

Okay let me give you this formula to show you what I mean. Assuming you have the same spreadsheet as me? in an empty cell use this formula

Please Login or Register  to view this content.

As you can see the answer is 3, where it should be 4. The problem is cell G4 where there is 3 names, this is the problem. The formula will not count the cells with more than one name. Trying here but these cells are problems.

Kind of, so you only want 72.48 in single cells that are complete? So if there is more than 1 name in a cell don't sum it? That is easy. Also when I count the 72.48s I need to divide by something to get the %. What am I dividing by?

Since there are more than one in a single cell, we decided to just take the entire completes (that is sorted in column J) against the total amount of column G (regardless of what is in that cell) and divide that number to equal the percentage. I feel like you might be pulling your hair out or screaming at your screen

lol you are cracking me up H. I thought that wasn't possible since there is more than one in each cell? I'm pretty sure what he wants is the completes versus the entire amount and those 2 amounts to be in a percentage. Like if the completed number is 80 out of 146, then the percentage would be 55%.

I edited my post lol. I don't think that is possible as there is more than 1 in a cell. What I meant was IF it says Complete then Count the completes against all the 72.48s. BUT the formula wouldn't pick up any 72.48s which have another number in a cell. Thus giving you the wrong % (And probably get you in the office for a telling off ) So i'm stumped obviously it is easy to count/sum as % with any 72.48s or 50.59s that are in single cells. No help I know but I really don't think thats possible. You could try posting it as a new thread? Now I think I have wasted your time (I soo didnt mean too) What do you think?

Well, first of all, you didn't waste my time! You have been so very, very helpful. Hmmm...I'm stumped too then. There isn't a way to to just tally the completed ones? Take the 72.48 and the 50.59 out of the equation, just all completed ones in column G with the entire total of column G (completed and not completed, or blanks), with a percentage...?

Okay here is another problem for you (Well it might be what you want, but I doubt it) So the completed in Column J = 85. In column G we have numerous text data so to get a number to divide by, we have to count something! So for example if the count was just 72.48 (Without anything next to it) So the answer for that is 4%. That is not what you want? The 72.48 in Column G is 3, and Column J is 85 Completes so 3/85 = 4%. The sum is not correct. Obviously this is an example and Complete is always going to be 85 because there are 85 in Column J. So you see how we need to tell Excel to only count certain cells with certain criteria. But we can't tell it to look in a cell with more than said criteria.

I know you don't have my updated one, but this is the best example I can give you. I count 88 completes out of a total of 139. I now need a percentage of the completed ones.

OMG I cant believe what a dummy I have been!!!!!! are you using COUNTIF to count the completes? and COUNT to count the cells in the Complete Range J? If so this is all you need =SUM(COUNTIF CELL ANSWER/COUNT CELL ANSWER) hit the % BUTTON in the HOME page tab and YES you have the % 88/139 = 63% yes?

that sounds like what I need, however your formula isn't working for me...?

Okay working on the sheet I have (I'm guessing you probably want this in one cell, which I will work on tomorrow. But for now this will get you through) In cell J 151 (On my sheet, assuming you want the total at the bottom of Column J?) use this

Please Login or Register  to view this content.

then in the cell under use this

Please Login or Register  to view this content.

Then for your total use

Please Login or Register  to view this content.

Sorry for the confusion I thought you wanted to sum the 72.48s and 50.59s. Could have given this to you ages ago. Thanks for friending me too, I accepted did you get that? Is this formula okay? You can move the COUNTIF & COUNTA to a different location and then put the = SUM in the Total column. Sorry it took so long, me being a dummy lol

I wish you'd stop being so hard on yourself And thanks for accepting my friend request. Ok, so the 2nd formula isn't working, when I hover my mouse is says if(typeof(autoid)=== 'undefined')....?

ok all of a sudden the second one works, but the last one to divide it does not lol

What cell do you want the % to show? What is the range (J2:J?)

And what is the number in the divide? #DIV? or like 0.16543 not that exactly but simular

ugh...now I'm lost and I have to leave I'll try again tomorrow...think I'm brain dead from all of this. You on the other hand have been rockin' it

Awww...hugs, thx. I'll let you know tomorrow how dumb I am lol bye for now H

Have you pressed the % button on the HOME page and in the NUMBER section? you can then use the increase or decrease buttons to round up or down. Or I could give you the =ROUND function which does the same thing. Did you look at the sample I posted? And I need to test something to double check, I thought of it as I was driving this morning. Think I was brain dead yesterday too

Hi there. No, I clicked on the cell and formulated it. But I'll undo that and use what you said. What I also can't figure out is my total of non-completes (n/a, not signed, not started and blanks) come to a total of 55 I was curious, so I counted manually), but my formula came out with a total of 99? What's up with that? lol

Thats what I was thinking this morning. Give me 5 mins I almost have it, just a few more clicks lol

lol you should be paid for this. Ok my other issue is that I used the percentage format, but I don't see where I can increase, decrease or round off...? My only option is the decimal of which I have set to 0, but percentage is still high.

Okay here you go use this formula to count all cells including blanks. This needs to be at the bottom somewhere(out the way if u like)

Please Login or Register  to view this content.

Then use this to count the "Completes" Again this can be out the way as its only needed to divide by

Please Login or Register  to view this content.

Now use this formula in the Total column where you want to see the %

Please Login or Register  to view this content.

And remember the SUM is COMPLETE/TOTAL otherwise the % will be too high. Girlwithdog remember to change the cell ranges to fit your spreadsheet I think you said you r one goes to row 139? so you will need to adjust that slightly. Let me know how you get on. My answer is 58% is that how you want your answer to look?

Lol in your one you just had the sums the wrong way around. So =SUM(E170/E171) gives you 60%.

:slaps her forehead: ok, ok oy vey lol. I'm still having issues with my blanks, non completes, still adding up to 146 as you can see when with what I sent...

You are losing me again the 146 is the whole column so the 88 is completes the sum is saying divide 88 completes by the cells in that column. If you only want to divide by any text in the column then remove the

Please Login or Register  to view this content.

Also your range should be E2:E148 not E2:E147 as you miss a complete in cell E148. And your COUNTIF is E2:E151??? is the example of the column you posted the exact sheet column you have? Once removed i'm seeing 89%. If that is wrong. tell me what the answer should be, by writting for example completes = 88 and the number of cells = 150. Now I think I'm making this sound more complex than it is, sorry

Probably the best thing to do is tell me in writing what results you want. So Completes divided by? Obviously there are things like Not started, Not signed etc.

Yipee lol, any probs just pm or reply if your boss asks you to do anything else. Glad I could help

You totally rock H!! And sent you a PM the other day not sure if you recieved or not, but I will let you know how it goes with him

Yeah I got it ta(he blushes) take care