SUMIF Function, peoblems autofill reversing

Hey,
sry i didn't know how to describe the problem better than this. I'll try in the text.

I have to do a sheet for my University. I'm using the SUMIF function to lookup certain names and the times they were there and how long. So my area are the names, my searching field is a certain name on a list and the area calculated are the hours behind the names in another list were the names are listed randomly.

so it looks something like this SUMIF[Doc1]sheet!DRD$4:DRD$400;$A2;[Doc1]sheet!DRE$4:DRE$400

This is working fine as long as im just pulling it down and the $A2 is the only thing changing. Problem is once i start pulling to the right it messes everything up and i dont know why. What i need is every fourth column ( like DQR-->DQV-->DQZ), but hwat happens when i pull it to the right (even when i give Excel like 3 handmade rows) is something like this: I stop with DQV-->DQZ and then Excel starts with DQX and after that always making 4 steps. Or it goes back 4 letters or some other bs like that.

I tried remaking the whole thing. I searched google (even though i honestly have to say i didnt really know what to search for). And i searched various other sides.

Every help is appreciated, sry for my sometimes bad English im a German trying to learn english right now

thx all of you and have a nice day

Joel

Update: I just tried it with a really simplified form one Excel sheet. And still i cant get it to work... but the error changed a little bit. Well basically the error stayed the same. What it does now is: What i gave him was A$1:A$3 and the next one C$1:C$3. If i pull htat to the right it gives me C$1:C$3 again and after that E... but then E again so it basically doubles everything even though I've never done that. I'm confused.

Well if im adding the $ signs were you did then the whole thing is absolut and nothing will ever change if im not mistaken. But i need it to change so i get the different values from the different days. But i will try, thx for the reply Speshul

Alright i uploaded the simplified File. Its very simplified though and the error im getting there is a little bit different from the other error. But its the same in the way that excel is not counting right or im doing something wrong(Its probably me :D )

thx Joel

Originally Posted by JeteMc

Am I correct in thinking that you just need to get the numeric values to match the names?

Yes you are. First I need the values to match the names in the list. The sum comes later, but i thought it would work with the SUMIF too.
Ok i just looked at yours and i have some problems getting through what you did there. But it seems to solve my problem. So thank you very very much!!! I'll look at it tomorrow morning first thing and try to understand everything you did there and then if i still have problems I will write here again
Again thank you very much for your work i really appriciate it

Joel

This tip with the Evaluate formula is worth gold. I think im starting to get what you did there

I would so love to close this thread down, ut i just cant get it to work. I think i understood pretty much everything you did there and I was able to make a new working test sheet of my own with your formula. Problem is i cant get it to work on the University stuff.
The first problem i encountered is that its not starting at 0,1 or 2 but at DQR9 and when i try to put that into your formula its not working. Quick example:

=INDEX(INDIRECT(ADRESS('[Hours]PLAN'!$DQR$9you only got a single number in here but when I'm doing this it gives me an error;'[Hours]PLAN'!$DQR$9))

I just dont know how to get it to work. If I break your formula down its like you searched for the list of hours then you went into the MATCH(Comparison) got the name you are searching for with the $A8 and then got the list of names. The return is the colum where your number is in and then it returns the Number/Hours which belongs to the name on this specific day.
Im getting either REF or Value errors. I tried it several times now and i even changed your formula a bit because i wanted to try to get the value for just one column. so I went like INDIRECT($DQS$9:$DQS$384) just to simplify it a bit (chance for less errors :D )

If my question doesn't make any real sense to oyu and you think i should just try it again just tell me i don't want you to feel like you are doing my work

alright thx a lot I'll try that after Lunch and see if that works for me if not ill make a sheet on how it looks like and upload that.
Really appreciate your dedication to help me I would be sooo lost without your advice

Still running into the same problem. If i try to type in just the number 9 it wont let me do it. Like that ADDRESS([Hours]PLAN!9; ..... it will highlight this as black once i press enter. Well i go to Lunch and then do the whole thing over again and see if it is working then.

Alright i got it working when i just put it together on one sheet. Problem is i need to pull the Data for the calculations later on from the server. And once i start with the reference [HOURS]PLAN! i cant get it to work and it won't let me do it. Problem as described above. So i guess that is the last error and once i get rid of these once everything should be fine.
Thx for all the great help.

You are an Excel SUPERHERO I tried it and at first didn't get it to work bur i got a #NV error so i kinda knew my (or your) formula had to be right. So i changed something here and there and worked through it again and now it is showing the right value for the person. Now i just have to get everything right so i can pull it and make it automatic for the rest of the people and days. Thx a lot for your help!!!!!
Shall i close this Thread or shall i leave it open in case i have another question when i come to pulling it out (don't know how to describe it better hope you understand me).
THX THX THX

Alright thx for helping again and yeah you made the rest of my day much better really Glad you could help me out here. Already upvoted you and everything )

Ok im running into a new Problem i implemented everything and all my formulas are working. only thing is i cant use the auto fill handle. I have to columns with the formula you provided. And what it needs to do is change from A1 to B1, but what it actually does once i pull two colums together it changes A1 to C1. I tried a lot of different things like adding more rows by hand and then using the auto fill and so on but nothing seems to work.