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.

 

[Smitty]

Have you tried SUMIF?

Smitty

 

[Yogi Anand]

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.

Hi bullit_nl:

Please clarify what you mean by '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.'

 

[bullit_nl]

Ok I'll try to explain myself better.

In column B I type different text. This happens on all 6 or 7 sheets in column B. It happens alot that the same text is typed in column B in different sheets. In column E (so not D like i said before) i have a total sum of different columns together, also on all sheets.

What I want is excel to recognize for me all same text entry in column B on all sheets and sum the total of column E from all sheets for that text entry.

Sorry for the row thing in previous post.

 

[Pekkavee]

I think SUMIF would do this.
Look in Excel-help.



Something like this:

Sumif.xls
	A	B	C	D
1
2
3				Valueforsearch
4	Matt	1		Will
5	Will	2
6	Lisa	1
7	Matt	2		22
8	Will	3
9	Lisa	4	FormulainEnglish
10	Will	2
11	Lisa	1	=SUMIF('Sheet1'!A4:A13,'Sheet1'!D4,'Sheet1'!B4:B13)+SUMIF('Sheet2'!A4:A13,'Sheet1'!D4,'Sheet2'!B4:B13)
12	Matt	2
13	Will	3
14
Sheet 1

Sumif.xls
	A	B	C	D
1
2
3
4	Matt	3
5	Will	4
6	Lisa	2
7	Matt	3
8	Will	4
9	Lisa	3
10	Will	2
11	Lisa	3
12	Matt	4
13	Will	2
14
Sheet 2

 

[Aladin Akyurek]

If you have the morefunc add-in installed...

On the Summary sheet:

SUMPRODUCT(--(THREED(Sheet1:Sheet6!$B$2:$B$100)=$B2),THREED(Sheet1:Sheet6!$E$2:$E$100))

where B2 houses a text condition/criterion.

Without morefunc

Enter the sheet names in column A from A2 on, select the sheet names, go to the Name Box on the Formula Bar, type Sheets, and hit enter. And use for the conditional calculation that you need:

=SUMPRODUCT(SUMIF(INDIRECT("'"&Sheets&"'!B2:B100"),$B2,INDIRECT("'"&Sheets&"'!E2:E100")))

 

[bullit_nl]

If you have the morefunc add-in installed...

On the Summary sheet:

SUMPRODUCT(--(THREED(Sheet1:Sheet6!$B$2:$B$100)=$B2),THREED(Sheet1:Sheet6!$E$2:$E$100))

where B2 houses a text condition/criterion.

Without morefunc

Enter the sheet names in column A from A2 on, select the sheet names, go to the Name Box on the Formula Bar, type Sheets, and hit enter. And use for the conditional calculation that you need:

=SUMPRODUCT(SUMIF(INDIRECT("'"&Sheets&"'!B2:B100"),$B2,INDIRECT("'"&Sheets&"'!E2:E100")))

Hi Aladin,

I installed the Morefunc add-in.

Where do i put the formula:

SUMPRODUCT(--(THREED(Sheet1:Sheet6!$B$2:$B$100)=$B2),THREED(Sheet1:Sheet6!$E$2:$E$100))

I have a sheet named totals, there i tried to paste your formula there(didn't work). Do i have to use a = (sign)? Btw in my sheets the text and numbers in columns B and E start from row 10.

What u mean with: where B2 houses a text condition/criterion?

Last thing. Does it matter if i want to have each sheet a different name instead of Sheet1, Sheet2, etc?


Thanks

 

[Aladin Akyurek]

If you have the morefunc add-in installed...

On the Summary sheet:

SUMPRODUCT(--(THREED(Sheet1:Sheet6!$B$2:$B$100)=$B2),THREED(Sheet1:Sheet6!$E$2:$E$100))

where B2 houses a text condition/criterion.

Without morefunc

Enter the sheet names in column A from A2 on, select the sheet names, go to the Name Box on the Formula Bar, type Sheets, and hit enter. And use for the conditional calculation that you need:

=SUMPRODUCT(SUMIF(INDIRECT("'"&Sheets&"'!B2:B100"),$B2,INDIRECT("'"&Sheets&"'!E2:E100")))

Hi Aladin,

I installed the Morefunc add-in.

Where do i put the formula:

SUMPRODUCT(--(THREED(Sheet1:Sheet6!$B$2:$B$100)=$B2),THREED(Sheet1:Sheet6!$E$2:$E$100))

I have a sheet named totals, there i tried to paste your formula there(didn't work). Do i have to use a = (sign)? Btw in my sheets the text and numbers in columns B and E start from row 10.

What u mean with: where B2 houses a text condition/criterion?

Last thing. Does it matter if i want to have each sheet a different name instead of Sheet1, Sheet2, etc?


Thanks

Insert 2 new sheets, name them First and Last, put all the relevant sheets between First and Last, and use, e.g., in C2 on the totals sheet:

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

$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.

 

[bullit_nl]

Pekkavee,

I dont have a Value for search column. I want to see on one sheet all the totals from sheet 1:sheet 7 entered in column D matching the text from column B. (hope this still sounds logical).

 

[Pekkavee]

Well, I gave you just an idea and an example.

If you write Will or Matt or some name in D4 you get total sum in D7. ( D4 cell works like $B2 in Aladins solution)

Never mind.
Use Aladins solution anyway.
It is more sophisticated, ease to write and easy to use with 7 sheeets of yours.

Regards
Pekka