Count rows with values in columns

[fitzbg]

Hello,

I am trying to count the total number of rows that contain at least 1 occurrence of specific values.

In the shared workbook there are 2 worksheets: Items and Components.

Each item has components entered in any of the columns next to it.

I want to calculate how many items use each component. I have entered the expected results in red. (Issue is about COUNTING ROWS.)

Please note this is not the same as calculating how many times each component is used. I already have a formula for that. (Issue is NOT about COUNTING CELLS.)

Thank you.

Fitz Barrientos


https://docs.google.com/open?id=0B4D72jdwscACYWY0ODRjOTYtNTBlMy00NGJlLWI0ZTUtNGYxMTA1NzFjNjZm


---

 

[Luke M]

In some helper column:

=COUNTIF(A1:Z1,SpecificValue)>0


Then do a countif on the helper column, like so:

=COUNTIF(HelperColumn,TRUE)

 

[fitzbg]

Luke,

Did you look at the shared workbook?

My sample only has a few items and a few components, but in reality we are talking about thousands of both.

The whole point is not to use helper columns. Otherwise I would have to add one extra column per component.

Thanks.

FB

 

[fitzbg]

I apologize my first message was not clear enough.

Using helping columns defeats the purpose of what I am trying to accomplish.

We have thousands of items and thousands of components.

Moreover, the components of an item can change at any time, and also components are added/deleted on a regular basis.

Maintaining the helper columns every time this happens would be almost impossible.

FB

 

[Kyle McGhee]

fitzbg,


Define this named formula name = MMULTARRAY2

=TRANSPOSE(--(Items!$B$1:$G$1=Items!$B$1:$G$1))

You may want to make this dynamically expand/contract based on the number of columns used


On the compenents tab, enter this in Column D2 and drag down to D12.

=SUMPRODUCT(IF(MMULT(--(Items!$B$2:$G$18=Components!A2),MMULTARRAY2)>=1,1,0))

The results are the same as the red. I cannot vouch for the performance of this over thousands of rows, but it will work with no helper columns.

 

[fitzbg]

Thank you, Kyle.

That's exactly what I needed.

FB

 

[SirJB7]

@Kyle McGhee

Hi!

Chapeau, monsieur... wow...

Regards!