Recognize text and count numbers

[bullit_nl]

Hello,

Can excel recognize if i used the same text on multiple sheets?

For example i have 6 sheets. On every sheet in row B there can be typed text. This text can change alot but will be used on all sheets in row B.

On all sheets i have numbers in row D. What i want is excel to recognicze for me all the same text in row B on all sheets and make a total from the numbers behind it from row D. I prefer to have this total on a seperate sheet.

 

[bullit_nl]

Aladin,

I get a error message on the formula. THREED becomes selected and B2 gets a blue border.

$B2 is a cell on your totals sheet. This cell houses the condition that must hold for the range in column B on all sheets in order to total the range in column E on all sheets.

Does this mean i have to type the text into B2 on my total sheet witch i want to be totaled?

 

[Aladin Akyurek]

...Does this mean i have to type the text into B2 on my total sheet witch i want to be totaled?

Yes.

An example to show what I understood your request to be:

Given the following example sheets...

Book3
	A	B	C	D	E	F
10		Klaas			60
11		Marie			80
12		Klass			40
13		Linda			70
14
15
VerkopenJun04

Book3
	A	B	C	D	E	F
10		Marie			60
11		Marie			80
12		Ben			90
13		Linda			100
14
VerkopenJul04

How to calculate the totals for Marie, etc.?

Put these two sheets between two empty sheets you could name First and Last and use in the totals sheet...

Book3
	A	B	C	D
1
2		Ben	90
3		Klaas	60
4		Linda	170
5		Marie	220
6
totals

The formula in C2 is:

=SUMPRODUCT(--(THREED(First:Last!$B$10:$B$100)=$B2),THREED(First:Last!$E$10:$E$100))

which is copied down.

As you can see from the exhibit, the formula totals the values associated with Ben, Klaas, etc.

If this is not what you wanted, try to re-describe the problem you want to solve.

 

[bullit_nl]

This example works now. I had to replace the "," for a ";" in the formula (sorry).

This is also very helpfull but not exactly what i'm looking for. Sorry for not explaining myself too well.

Actually what i want is:

to see on the totals sheet all text used in columns B (not having to type it in myself), cause i don't know what different text is typed in columns B. But i want excel to recognize the same text entry's so that i don't see dubbel entry's on the totals list. Then i also need to have the correct totals from all columns E matching B.

Maybe this description is better?

 

[Pekkavee]

" This example works now. I had to replace the "," for a ";" in the formula (sorry)."

This problem we have in Europe, I think.



"Actually what i want is:

to see on the totals sheet all text used in columns B (not having to type it in myself), cause i don't know what different text is typed in columns B. But i want excel to recognize the same text entry's so that i don't see dubbel entry's on the totals list. Then i also need to have the correct totals from all columns E matching B. "

For this you need a macro which is doing the following:
1. Copy all name lists from all data sheet to total sheet leaving dublicates away.
2. Sets in Aladins formula in all needed cells depending on the number of names.

I am sure someone here can do it.

 

[Aladin Akyurek]

This example works now. I had to replace the "," for a ";" in the formula (sorry).

This is also very helpfull but not exactly what i'm looking for. Sorry for not explaining myself too well.

Actually what i want is:

to see on the totals sheet all text used in columns B (not having to type it in myself), cause i don't know what different text is typed in columns B. But i want excel to recognize the same text entry's so that i don't see dubbel entry's on the totals list. Then i also need to have the correct totals from all columns E matching B.

Maybe this description is better?

aa3D ConditionalSum bullit_nl v2.xls
	A	B	C	D
1		4
2		Ben	90
3		Klaas	100
4		Linda	170
5		Marie	220
6
totals

Formulas...

B1:

=SUMPRODUCT(--(UNIQUEVALUES(THREED(First:Last!B2:B100))<>""))

B2:

=IF(ROW()-ROW(B$2)+1<=$B$1,INDEX(UNIQUEVALUES(THREED(First:Last!$B$2:$B$100),1),ROW()-ROW(B$2)+1),"")

which you must confirm with control+shift+enter instead of just with enter and copy down.

C2, copied down:

=IF(B2<>"",SUMPRODUCT(--(THREED(First:Last!$B$10:$B$100)=$B2),THREED(First:Last!$E$10:$E$100)),"")

Note. Don't forget to switch from comma (",") to semi-colon (";").

 

[Pekkavee]

Cool Aladin,

I didn't have a clue on UNIQUEVALUES.

I have to study them.

Thanks a lot, Aladin. You are our best teacher.

Regards
Pekka

 

[bullit_nl]

Formulas...

B1:

=SUMPRODUCT(--(UNIQUEVALUES(THREED(First:Last!B2:B100))<>""))

B2:

=IF(ROW()-ROW(B$2)+1<=$B$1,INDEX(UNIQUEVALUES(THREED(First:Last!$B$2:$B$100),1),ROW()-ROW(B$2)+1),"")

which you must confirm with control+shift+enter instead of just with enter and copy down.

C2, copied down:

=IF(B2<>"",SUMPRODUCT(--(THREED(First:Last!$B$10:$B$100)=$B2),THREED(First:Last!$E$10:$E$100)),"")

Note. Don't forget to switch from comma (",") to semi-colon (";").

Aladin,

This is great and easy to use!

I only had to change the formula in B2 from $B$2:$B$100 to $B$10:$B$100 cause that's were i start.

I'm not sure if i have to change other formulas from B2 to B10 (please let me know if i have to)?

Futher i only can say: WOW and thanks alot!!

 

[Aladin Akyurek]

...

I only had to change the formula in B2 from $B$2:$B$100 to $B$10:$B$100 cause that's were i start.

I'm not sure if i have to change other formulas from B2 to B10 (please let me know if i have to)?...

Oversight on my part...

Change just those B2's in the Last bit and nowhere else...

B1:

=SUMPRODUCT(--(UNIQUEVALUES(THREED(First:Last!B10:B100))<>""))

B2, to be confirmed with control+shift+enter.

=IF(ROW()-ROW(B$2)+1<=$B$1,INDEX(UNIQUEVALUES(THREED(First:Last!$B$10:$B$100),1),ROW()-ROW(B$2)+1),"")