Count given values in column if other columns contain data.

[Halfy]

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.

[cityinbetween]

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

[Halfy]

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)

[ConneXionLost]

How about:

Please Login or Register  to view this content.

Cheers,

[Halfy]

How about:

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.

[Halfy]

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.