formula to search and analyse.

**bluefrog** · 06-03-2008, 01:49 PM

I'm in the process of trying to make a worksheet to analyse the data in 12 other sheets by input.

Basically put, there are 10 variables (1-10 in text form with the '/' character in them) which need to be searched in 12 worksheets (named in months jan - dec) .

I need the formula to search all of the worksheets and every time it finds the searched character string, if data is in one column (profit), add it to the result but if in another column (loss) ignore it.

I also need another formula, similar to this one, to add all of the searched items together i.e. search ten/ and the there are 8 instances of the input string in the worksheets, therefore the result is 8. Or search twelve/ and there are 24 in the worksheets so the result is 24.

Is this possible? Anybody clever enough to know how to even start looking at this?

Recap:

10 search terms (one/, two/, three/, etc..)
12 worksheets (Jan, Feb, Mar, Apr, Jun etc...)

Thanks in advance for any help you can give.

Darren

**shg** · 06-03-2008, 02:32 PM

Please read the Forum Rules about thread titles, and then edit your thread title to be descriptive of your problem.

**bluefrog** · 06-04-2008, 06:46 AM

Sorry Shg, changed to best describe it in my own way. Probably still not right though.

D

**shg** · 06-04-2008, 07:25 PM

I think a sample workbook with some dummy data would help someone make a suggestion.

**bluefrog** · 06-05-2008, 11:14 AM

You are right of course. I have now attached a dummy sheet with sample data to just give a feel for what I need to achieve.

Each sheet (jan - dec) will have data entered into it based on 'variable' names, one/1 two/2 etc... upto 10. Basically what needs to happen is that a formula needs to check each worksheet for the occurance of the 'variable' and if present check the profit & loss columns to see what data is there. And if the data is in profit add one to the P column on the analytics sheet for the 'variable' and month or in loss add one to the L column under the same rules.

So based on this the following 3 months would read:

JAN=
one/1=1 (p column)
two/2=2 (p column)
three/3=1 (p column)
four/4=0
five/5=1 (p column)
six/6=1 (p column) & 1 (L column)
seven/7=1 (p column)
eight/8=0
nine/9=0
ten/10=3 (L column)

FEB=
one/1=1 (p column) & 1 (L column)
two/2=3 (p column)
three/3=2 (p column) & 3 (L column)
four/4=3 (p column) & 1 (L column)
five/5=0
six/6=0
seven/7=0
eight/8=0
nine/9=0
ten/10=0

MAR=
one/1=0
two/2=0
three/3=0
four/4=1 (L column)
five/5=1 (L column)
six/6=2 (p column) & 2 (L column)
seven/7=0
eight/8=3 (P column)
nine/9=4 (L column)
ten/10=0

See attached worksheet for verification. I'm not sure if a vlookup & count would work? Not sure it would based on possible multiple rows.

Thanks in advance.

Darren

**bluefrog** · 06-07-2008, 01:03 PM

Anybody even got an idea where to start with this?

**shg** · 06-08-2008, 12:27 PM

In Analytics!B5: =SUMPRODUCT( (INDIRECT($A5 & "!d2:d31") = B$3) * (INDIRECT($A5 & "!f2:f31") > 0) )

In Analytics!C5: =SUMPRODUCT( (INDIRECT($A5 & "!d2:d31") = B$3) * (INDIRECT($A5 & "!g2:g31") > 0) )

Select those two cells, and then drag them right and down.