Sumproduct formula problems

[BeSmart]

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.

 

[Lars-Åke Aspelin]

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

 

[BeSmart]

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