Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Counting rows with 3 columns
Hi,
I have three colums and I need to perform some counting but can't quite get it right. Each column could possibly contain a single character, 'Y' or nothing at all. I need to count the rows where the character 'Y' is present in at least one column. In other words, if a 'Y' occurs anywhere in a row, I want to count that row. Thanks in advance! |
#2
|
|||
|
|||
One way:
=SUMPRODUCT(--NOT(ISERR(SEARCH("Y",A1:A100&B1:B100&C1:C100)))) In article , Tuc wrote: Hi, I have three colums and I need to perform some counting but can't quite get it right. Each column could possibly contain a single character, 'Y' or nothing at all. I need to count the rows where the character 'Y' is present in at least one column. In other words, if a 'Y' occurs anywhere in a row, I want to count that row. Thanks in advance! |
#3
|
|||
|
|||
Assuming that Columns A, B, and C contain your data, try...
=SUMPRODUCT(--(COUNTIF(OFFSET(A1:C10,ROW(A1:C10)-MIN(ROW(A1:C9)),0,1),"Y" )0)) ....or the following formulas I picked up from Daniel M.... =SUMPRODUCT(--(MMULT(--(A1:C10="Y"),{1;1;1})0)) ....confirmed with just ENTER, and =SUMPRODUCT(--(MMULT(--(A1:C10="Y"),TRANSPOSE(COLUMN(A1:C10))*0+1)0)) ....confirmed with CONTROL+SHIFT+ENTER. Hope this helps! In article , Tuc wrote: Hi, I have three colums and I need to perform some counting but can't quite get it right. Each column could possibly contain a single character, 'Y' or nothing at all. I need to count the rows where the character 'Y' is present in at least one column. In other words, if a 'Y' occurs anywhere in a row, I want to count that row. Thanks in advance! |
#4
|
|||
|
|||
Correction...
=SUM(--(MMULT(--(A1:C10="Y"),TRANSPOSE(COLUMN(A1:C10))*0+1)0)) ....confirmed with CONTROL+SHIFT+ENTER. In article , Domenic wrote: =SUMPRODUCT(--(MMULT(--(A1:C10="Y"),TRANSPOSE(COLUMN(A1:C10))*0+1)0)) ...confirmed with CONTROL+SHIFT+ENTER. |
#5
|
|||
|
|||
I think this'll do what you're asking:
=SUMPRODUCT(--(LEFT(A1:A100&B1:B100&C1:C100,1)="Y")) Regards, Ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
hidden rows & columns slow file open | Excel Discussion (Misc queries) | |||
Removing Near-Duplicate Rows, Leaving Those w/Most Data in Specific Columns | Excel Discussion (Misc queries) | |||
How to keep rows together when sorting columns? | Excel Worksheet Functions | |||
Convert multiple columns to rows | Excel Worksheet Functions | |||
interchange columns with rows | Excel Discussion (Misc queries) |