| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
totalling values based on conditions
Thank you.
"Lars-Ã…ke Aspelin" wrote: I will make an attempt to explain the differnet parts of the formula, which by the way is not a SUMIF but a SUMPRODUCT. =SUMPRODUCT(- -(A$1:A$12=A2),- -(B$1:B$12),- -(ROW(A$1:A$12)MAX(ROW(A$1:A$12)*(- -(A$1:A$12<A2)*(- -ROW(A$1:A$12)<ROW(A2))))),- -(ROW(A$1:A$12)<1/MAX(1/ROW(A$1:A$12)*(- -(A$1:A$12)<A2)*(- -ROW(A$1:A$12)ROW(A2))))) (A$1:A$12=A2) This filters out rows with the same tier level (or whatever the number in column A is called (B$1:B$12) This is the actual weights to be added. (maybe this part should have been the first one as all other parts are different kinds of filters) (ROW(A$1:A$12) Only include rows after a certain startrow MAX(ROW(A$1:A$12)*(- -(A$1:A$12<A2)*(- -ROW(A$1:A$12)<ROW(A2))))) The startrow is the row with highest, MAX, rownumber where the tier is less than the tier on the current row but comes BEFORE the current row (ROW(A$1:A$12)< Only include rows before a certain stoprow ROW(A$1:A$12)<1/MAX(1/ROW(A$1:A$12)*(- -(A$1:A$12)<A2)*(- -ROW(A$1:A$12)ROW(A2) The stoprow is the row with lowest rownumber where the tier is less than the tier on the current row but comes AFTER the current row. The inversion of the maximum inverted rownumber is used here insted of the minimum row number in order not to get row number 0 as the stoprow. This result could probably be achieved with a shorter formula, but I leave that for someone else to try to figure out. Lars-Ã…ke On Mon, 28 Jul 2008 14:28:45 -0700, Sherry wrote: It works! That is the longest SUMIF I've ever seen, but I don't care, as long as it works! I will not know the last row, but I think I can get there from here. Thank you again. "Lars-Ã…ke Aspelin" wrote: On Mon, 28 Jul 2008 13:19:52 -0700, Sherry wrote: Column B is Tier and Column D is Weight: =SUMIF(B7:B29,"=1",D7:D29) I cannot use this same formual because it would tally all the 2's and then all the 3's. I need the tallies to be by group. "Lars-Ã…ke Aspelin" wrote: On Mon, 28 Jul 2008 10:40:01 -0700, Sherry wrote: I have 2 columns of data. One contains the "tier" or 1, 2, or 3. The other column contains a weight (%). The sum of all tier "1" must = 100%. The sum of all tier "2" for a tier 1 must sum to 100%. The sum of all tier "3" for each tier 2 must sum to 100%. I need to validate that the weights are correct. I've validated Tier "1" with Sumif, but I'm having trouble with tier 2 & 3. Then I tried using VBA, but I am having difficulty. Can anyone help with a simple approach? Thank you!!! Sherry Tier Weight 1 50% 2 30% 3 10% 3 90% 2 70% 1 50% 2 40% 2 60% 3 50% 3 50% Which formula did you use to validate the weightsum for Tier "1"? Can you not use exactly the same method for Tier "2" and Tier "3" by just replaceing "1" with "2" and "3" respectively? Lars-Ã…ke OK, I did not understand what tiers werw I guess. Assuming that your data are in columns A and B from row 2 to row 11 and that the cells A1 and A12 are blank (or zeroes) The data in columns A and B have to be numbers, not text. Try the following formula in cell C2: Note: This is an array formula that has to be entered by CTRL+SHIFT+ENTER rather than just ENTER =SUMPRODUCT(- -(A$1:A$12=A2),- -(B$1:B$12),- -(ROW(A$1:A$12)MAX(ROW(A$1:A$12)*(- -(A$1:A$12<A2)*(- -ROW(A$1:A$12)<ROW(A2))))),- -(ROW(A$1:A$12)<1/MAX(1/ROW(A$1:A$12)*(- -(A$1:A$12)<A2)*(- -ROW(A$1:A$12)ROW(A2))))) (all in one line) Copy this formula down from C2 to C11 and there will be a 1 (100%) for each row that the corresponding tiers have the correct sum (100%) If e.g. cell B5 is faulty and has 19% rather than 90% you will get 0.29 (29%) in cells C4 and C5 indicating that the sum of B4 and B5 is not correct. Hope this helps / Lars-Ã…le |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to determine the values based on given conditions? | Excel Discussion (Misc queries) | |||
| Isolate and total cell values based on conditions | Excel Discussion (Misc queries) | |||
| Totalling unique values | Excel Discussion (Misc queries) | |||
| Cell values based upon multiple conditions | New Users to Excel | |||
| Sub-Totalling Based upon a changing field | Excel Worksheet Functions | |||
Hybrid Mode
