P
Peter
I recently got a request to total up throughput for different product types (Product A$ & B$) by individual (Col's C & D). The only rule added is that if more than one person worked on this project (i.e. Col C and Col D are populated with different names), then the dollar amount for whatever product you are looking at gets divided by the number of people who worked on it. For simplicity, I only show two. However, I used counta and divide by counta if there were every more than two. There is also an extra tidbit added at the end to spice up this request a bit...but I would like to ask this portion first. Anyway, the data looks like this
Product A$ Product B$ Col C Col D Col E Col F.............Col J (table starts) Col
100 500 person a person b formula formula formul
100 500 person a
100 500 person b person
I got this to work, but it is a bit burdensome. I used this formula in column e..... if(counta(c1:d1)=1,a1,a1/(counta(c1:d1))....that return the dollar amount that each person would get for product a$. I did the same thing in column F for product b$. Then, I created a table of names which represent the persons in Col C & D, let's say starting at J. Then, in the column next to the name, I chose to put that individuals total for product a$. To get that total, I used....
=SUMIF(C1:C3,J1,E1:E3)+SUMIF(D1
3,J1,E1:E3).....That got me what I needed, by also took away the flexibility to add the potential for three people working together. Using CountA had that flexibility built in, but I couldn't get it to work. Also, something weird (I think) happened when I had the sumif covering C13. It recognized person a's name and knew it had to assign a value, but when it assigned the value, if person a showed up in column C, it gave column E's value (as expected). If person a, showed up in column D, it gave the value for column F. I tried to used absolute on E1:E3, but it didn't change anything. It still summed outside the zone I specified.....weird
Anyway, I know it can be done this way....but where did I go wrong with doing this an easier way??
Also, the next piece.....the person came back and said they will want to use this as a running list. So, I had to be able to allow rows to be inserted. I chose a macro to insert a row and copy the formulas up a row. Worked out ok....had I known that up front, what would be your recommendations? Pivot tables are a stretch for me on this one as I have very very little knowledge of them, but it seems this might be the optimum choice here. If so, please walk me through what I should have done
Thank you in advance.....sorry about the lengthy note....you can probably imagine how long I spent on getting my results from the above methodology....yikes
Have a great day everyone
Pete
Product A$ Product B$ Col C Col D Col E Col F.............Col J (table starts) Col
100 500 person a person b formula formula formul
100 500 person a
100 500 person b person
I got this to work, but it is a bit burdensome. I used this formula in column e..... if(counta(c1:d1)=1,a1,a1/(counta(c1:d1))....that return the dollar amount that each person would get for product a$. I did the same thing in column F for product b$. Then, I created a table of names which represent the persons in Col C & D, let's say starting at J. Then, in the column next to the name, I chose to put that individuals total for product a$. To get that total, I used....
=SUMIF(C1:C3,J1,E1:E3)+SUMIF(D1
3,J1,E1:E3).....That got me what I needed, by also took away the flexibility to add the potential for three people working together. Using CountA had that flexibility built in, but I couldn't get it to work. Also, something weird (I think) happened when I had the sumif covering C13. It recognized person a's name and knew it had to assign a value, but when it assigned the value, if person a showed up in column C, it gave column E's value (as expected). If person a, showed up in column D, it gave the value for column F. I tried to used absolute on E1:E3, but it didn't change anything. It still summed outside the zone I specified.....weirdAnyway, I know it can be done this way....but where did I go wrong with doing this an easier way??
Also, the next piece.....the person came back and said they will want to use this as a running list. So, I had to be able to allow rows to be inserted. I chose a macro to insert a row and copy the formulas up a row. Worked out ok....had I known that up front, what would be your recommendations? Pivot tables are a stretch for me on this one as I have very very little knowledge of them, but it seems this might be the optimum choice here. If so, please walk me through what I should have done
Thank you in advance.....sorry about the lengthy note....you can probably imagine how long I spent on getting my results from the above methodology....yikes
Have a great day everyone
Pete