Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct formula problems
Hi All
I'm having trouble with a couple of formulas and I think I've written them wrong?? First Formula: In cell CB183 the formula looks at range($B$37:$H$165) to finds any occurrences of "production"or "installation" or "non commission" within any STRING of text (hence the "*XX*") and then adds those rows in range(CB37:CM165) that match. {=SUMPRODUCT(--ISNUMBER(SEARCH({"*production*";"*installation*";" *non commission*"},$B$37:$H$165)))*CB37:CM165)} The formula works when I type "production" into any cell within the range(B37:G165), but it doesn't work if I type the word "installation" or "non commission" into any cell in the range? Could someone tell me what I've done wrong here?? Second problem: I'm getting a circular reference error from the following formula - can anyone tell me what I've done wrong? =SUMPRODUCT(--($A$35:$A$165=$B188),--(MONTH($CB$10:$CM$10)=MONTH(H$10))*($CB$35:$CM$165 )) The formula looks up range ($A$35-$A$165)=$B188 and finds all occurrences of "Metro A Group". (The formula finds about 20 e.g. (1,1,1,1,1,0,0,1,...)) Then it looks up the month row ($CB$10:$CM$10) and finds the column that matches the date in cell H10. The formula finds this in the first column for Jan e.g. (1,0,0,0,0,0,0,0,0,0,0,...) Then it's suppose to sum all relevant values in range $CB$35:$CM$165 This is where I think it's falling over?? and I get the circular reference?? $CB$35:$CM$165 rows have formulas totaling each row within the table. I can't see any formulas within this range that cross calculate with any cells in my formula??? Any help would be greatly appreciated. -- Thank for your help BeSmart |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct formula problems
On Sun, 21 Feb 2010 04:41:01 -0800, BeSmart
wrote: Hi All I'm having trouble with a couple of formulas and I think I've written them wrong?? First Formula: In cell CB183 the formula looks at range($B$37:$H$165) to finds any occurrences of "production"or "installation" or "non commission" within any STRING of text (hence the "*XX*") and then adds those rows in range(CB37:CM165) that match. {=SUMPRODUCT(--ISNUMBER(SEARCH({"*production*";"*installation*";" *non commission*"},$B$37:$H$165)))*CB37:CM165)} The formula works when I type "production" into any cell within the range(B37:G165), but it doesn't work if I type the word "installation" or "non commission" into any cell in the range? Could someone tell me what I've done wrong here?? I don't think that your formula works as you expect even for "production". The formula, as states, does only return the value of cell CB35 if you enter a string containing "production" in any of the cells B35:H35 (not B35:G165). Nothing happens whatever you enter in any of the cells below row 35. But I don't think that is what you expect. What do you mean by "adds those rows"? Do you mean that if you have any of the listed words in any of the cells in columns B to H for a certatin row, then you want to add all values in the cells in columns CB to CM in that particular row. And them sum this behaviour for all rows from 37 to 165 to give the total result for your formula? Lars-Åke Second problem: I'm getting a circular reference error from the following formula - can anyone tell me what I've done wrong? =SUMPRODUCT(--($A$35:$A$165=$B188),--(MONTH($CB$10:$CM$10)=MONTH(H$10))*($CB$35:$CM$165 )) The formula looks up range ($A$35-$A$165)=$B188 and finds all occurrences of "Metro A Group". (The formula finds about 20 e.g. (1,1,1,1,1,0,0,1,...)) Then it looks up the month row ($CB$10:$CM$10) and finds the column that matches the date in cell H10. The formula finds this in the first column for Jan e.g. (1,0,0,0,0,0,0,0,0,0,0,...) Then it's suppose to sum all relevant values in range $CB$35:$CM$165 This is where I think it's falling over?? and I get the circular reference?? $CB$35:$CM$165 rows have formulas totaling each row within the table. I can't see any formulas within this range that cross calculate with any cells in my formula??? Any help would be greatly appreciated. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct formula problems
Hi Lars-Ã…ke
You're right - sorry, I didn't test the formula enough and assumed that it was working. I found the formula in this discussion group and I thought I could be smart and revise it to what I needed... BeSmart wasn't so smart... So your help would be greatly appreciatedA Yes your explanation of what I'm trying to do is correct. The user may enter any of these words (within a string) into the range B37:H165: "production" or "installation" or "non commission" I need to find the rows where they've entered any of these words and calculate the corresponding totals in columns CB37:CB165. (I now realize that I made an error and need to calculate each column in CB:CM separately). Then I'll copy the formula to the next column to calculate the corresponding totals in column CC37:CC165 and repeat the formula through to column CM. Sorry for the typo - the range is B37:H165, not B37:G165. I think I've worked out my second formula and what I was doing wrong - it now seems to be working and not causing a circular ref: =SUMPRODUCT(--($A$37:$A$165=$B188)*--(MONTH($CB$10:$CM$10)=MONTH(H$10)),$CB$37:$CM$165) I hope you can help? Thank you for your time Regards BeSmart "Lars-Ã…ke Aspelin" wrote: On Sun, 21 Feb 2010 04:41:01 -0800, BeSmart wrote: Hi All I'm having trouble with a couple of formulas and I think I've written them wrong?? First Formula: In cell CB183 the formula looks at range($B$37:$H$165) to finds any occurrences of "production"or "installation" or "non commission" within any STRING of text (hence the "*XX*") and then adds those rows in range(CB37:CM165) that match. {=SUMPRODUCT(--ISNUMBER(SEARCH({"*production*";"*installation*";" *non commission*"},$B$37:$H$165)))*CB37:CM165)} The formula works when I type "production" into any cell within the range(B37:G165), but it doesn't work if I type the word "installation" or "non commission" into any cell in the range? Could someone tell me what I've done wrong here?? I don't think that your formula works as you expect even for "production". The formula, as states, does only return the value of cell CB35 if you enter a string containing "production" in any of the cells B35:H35 (not B35:G165). Nothing happens whatever you enter in any of the cells below row 35. But I don't think that is what you expect. What do you mean by "adds those rows"? Do you mean that if you have any of the listed words in any of the cells in columns B to H for a certatin row, then you want to add all values in the cells in columns CB to CM in that particular row. And them sum this behaviour for all rows from 37 to 165 to give the total result for your formula? Lars-Ã…ke Second problem: I'm getting a circular reference error from the following formula - can anyone tell me what I've done wrong? =SUMPRODUCT(--($A$35:$A$165=$B188),--(MONTH($CB$10:$CM$10)=MONTH(H$10))*($CB$35:$CM$165 )) The formula looks up range ($A$35-$A$165)=$B188 and finds all occurrences of "Metro A Group". (The formula finds about 20 e.g. (1,1,1,1,1,0,0,1,...)) Then it looks up the month row ($CB$10:$CM$10) and finds the column that matches the date in cell H10. The formula finds this in the first column for Jan e.g. (1,0,0,0,0,0,0,0,0,0,0,...) Then it's suppose to sum all relevant values in range $CB$35:$CM$165 This is where I think it's falling over?? and I get the circular reference?? $CB$35:$CM$165 rows have formulas totaling each row within the table. I can't see any formulas within this range that cross calculate with any cells in my formula??? Any help would be greatly appreciated. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT problems | Excel Discussion (Misc queries) | |||
I, too, am having problems with SUMPRODUCT | Excel Worksheet Functions | |||
Sumproduct problems... | Excel Worksheet Functions | |||
SUMPRODUCT problems | Excel Worksheet Functions | |||
Problems with sumproduct | Excel Worksheet Functions |