# Count given values in column if other columns contain data.

1. ## Count given values in column if other columns contain data.

Hello,

I've been trying to get a new count to work, ever since I received my last answer on this site.
The new issue is...

I am trying to count the number of entries in a given column that begin with a certain text (Eg: cell value "AB" followed by a date, such as "AB 1 Jan 2014"), but only when any of three other columns contain any data at all (the values are generally "OK", "ERR", "CORR", or "1", but could be anything). If two or more of those other three columns contain data, then the whole row should only count once.

Eg:
...A...B...C...D
1..N...X.........
2..O.......X.....
3..N..............
4..N............X
5..N...X...X...X
Count the number of rows where "N" is in column A, and something exists in columns B, C, and/or D. In this case, end result is 3.

I am able to do this using helper columns. But if possible, I would like to avoid the use of those.

2. ## Re: Count given values in column if other columns contain data.

I believe you should be able to achieve this using the "=COUNTIFS" formula. Can you upload a sample workbook?

3. ## Re: Count given values in column if other columns contain data.

Samplebook.xlsx
Here is the sample.
The three columns that may contain data are L, O, and R. Only count rows that have something in at least one of these three columns.

The data to be counted is column A, for each starting letter combination (one cell will state the total for BH, another cell for MJ, etc.).

The sample spreadsheet can not be altered. I am referencing it in another spreadsheet to track productivity.
(For simplicity, I have removed about 3/4 of the columns, and 99% of the rows)

4. ## Re: Count given values in column if other columns contain data.

``Please Login or Register  to view this content.``
Cheers,

5. ## Re: Count given values in column if other columns contain data.

Originally Posted by ConneXionLost

``Please Login or Register  to view this content.``
Cheers,
This does not work. This would multiply the four columns logic value, so if a single one is false, the result for the row is negative.

6. ## Re: Count given values in column if other columns contain data.

Finally found it...

{=SUM(--(LEFT(AG2:AG1000,2)="BH")*(--(LEN(AH2:AH1000&AI2:AI1000&AJ2:AJ1000)>0)))}

Had to learn about -- and {} to get the solution... but it works.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1