Ask Your Question
0

Count rows and search for letter

asked 2016-05-20 12:16:04 +0200

SilvioSiefke gravatar image

updated 2016-05-20 12:32:25 +0200

Lupp gravatar image

Is there a ways in different worksheets and in a particular column is searched for a letter and the found are given in percent?

Example

     A         B      C
1   Hello      B      A
2   world      B      F

So is the table built and i want count in all spreadsheets in column C for A , for F or for A. The found letter should imaging in percent for all filled line in spreadsheet.
Hope is to understand, to explain is ever so hard.

(Table edited by @Lupp)

edit retag flag offensive close merge delete

Comments

Understand too hard. "percent" of what? From the total number of characters in the workbook? Do you need calculate the spaces? What do you have in mind when wrote "in column C for A , for F or for A"?

JohnSUN gravatar imageJohnSUN ( 2016-05-20 12:28:54 +0200 )edit

3 Answers

Sort by » oldest newest most voted
0

answered 2016-05-20 12:57:56 +0200

SilvioSiefke gravatar image

Hello,

yes found same. Thanks. Now it work.

=COUNTIF($'2003'.M2:$'2003'.M50;"B")/COUNTA($'2003'.A1:A50)

Silvio

edit flag offensive delete link more

Comments

This is not going accross many sheets.
The reference used with COINTIF is unnecessarily complicated.

Lupp gravatar imageLupp ( 2016-05-20 13:00:39 +0200 )edit

Unsolicited advice:
Do not use an extra sheet for each year if data are structured equally.
Do not use non-names (in this case numbers by syntax) for sheets.

Lupp gravatar imageLupp ( 2016-05-20 13:03:25 +0200 )edit
0

answered 2016-05-20 12:54:08 +0200

Lupp gravatar image

updated 2016-05-20 12:58:14 +0200

To do so actually accross an arbitrary number of sheets is not possible by a single formula, because the very few functions accepting 'cuboids' as parameters are not appropriate to fulfil the expectations. You would need something like

=COUNTIF($SheetFirst.$C$1:$SheetLast.$C$40;"A")/COUNTA($SheetFirst.$C$1:$SheetLast.$C$40)

COUNTA is capable of processing cuboids. COUNTIF is not.

Possible workarounds:
1. Rework the formula to explicitly reference each sheet on its own parameter position. (Fix limit for number of sheets!)
2. Use (at least) one helper column in each sheet, allowing to accomplish the task using SUM and COUNTA (or similar).
3. Code a user function for the purpose.

edit flag offensive delete link more
0

answered 2016-05-20 13:11:28 +0200

JohnSUN gravatar image

updated 2016-05-20 13:12:32 +0200

I think you need a PivotTable

PL

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-05-20 12:16:04 +0200

Seen: 118 times

Last updated: May 20 '16