Countif or Vlookup or something else?

[nparsons75]

Hi, I have a table which contains data in Col A (Part Description). I have a cell which I need to look at Col A, if Col A = Desc1, look in Column N and count the non blanks.

Is there a function out there to do this?

Thank you.

 

[tyija1995]

Hey, I'm trying to make sense of the question, your first column yields part descriptions, then you want to look at how many times a part description occurs and put it into column N? Is that right?

If so maybe a SUMPRODUCT formula would suffice:
=SUMPRODUCT(--("Description here"=$A$2:$A$100))

Where A2:A100 holds the part descriptions - change according to your data, and the "Description here" is one of the descriptions from Column A

 

[nparsons75]

Apologies, its hard to explain, let me try again.

So, I have one main table, (Table 2). In Col A of this table is a long list of different part descriptions. One of which is "Desc1". In Cell, A1 (just a blank cell) I would like to find out the number for the following...

Lookup Col A to find all occurrences of "Desc1". Then, look to other columns to find and count the number of non blank cells.


[TABLE="width: 500"]
<tbody>[TR]
[TD]Col A[/TD]
[TD]Col B[/TD]
[TD]Col C[/TD]
[TD]Col D[/TD]
[TD]Col E[/TD]
[TD]Col F[/TD]
[/TR]
[TR]
[TD]Desc1[/TD]
[TD][/TD]
[TD]date[/TD]
[TD]date[/TD]
[TD]date[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Desc7[/TD]
[TD][/TD]
[TD]date[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Desc1[/TD]
[TD][/TD]
[TD]date[/TD]
[TD]date[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

So, in an empty cell somewhere, lets say Cell A1, I need to return the following result.


Example,

For Col C, Desc 1 = 2
For Col E, Desc 1 = 1
For Col F, Desc 1 = 0

Hope that makes sense...Thanks for the help

 

[Special-K99]

Perhaps

in G1 of the same sheet
=SUMPRODUCT(($A$1:$A$1000=$A1)*(B$1:B$1000<>""))
or
=COUNTIFS($A$1:$A$1000,$A1,B$1:B$1000,"<>")
copy across as far as column J and down for as many rows as you have

Column G reflects column B, H reflects C, I reflects D, J reflects E

 

[tyija1995]

Hey, thanks for the clarifications, okay so I assume here your first row is blank (As you say cell A1 is blank) so I have used the top row for the following formula: I.e. Cell B1 would hold this formula (Then drag across to N1 or however many you columns):
=SUMPRODUCT(--("Desc1"=$A$2:$A$100),--(""<>B$2:B$100))

Where I assume your data is stored from cells A2 down to A100 - again change the range accordingly.

To visualise it would look like this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]=SUMPRODUCT(--("Desc1"=$A$2:$A$9),--(""<>A$2:A$9))[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Desc1[/TD]
[TD]Date[/TD]
[TD]Date[/TD]
[TD]Date[/TD]
[TD][/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Desc4[/TD]
[TD][/TD]
[TD][/TD]
[TD]Date[/TD]
[TD][/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Desc5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Desc1[/TD]
[TD]Date[/TD]
[TD]Date[/TD]
[TD][/TD]
[TD]Date[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Desc3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Desc2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Desc1[/TD]
[TD]Date[/TD]
[TD][/TD]
[TD][/TD]
[TD]Date[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Desc6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Date[/TD]
[/TR]
</tbody>[/TABLE]

Then by dragging that formula across the top row you should get the desired answer.

In this case: A1 through to F1 should give: 3; 2; 1; 1; 2; 2

Hope this makes sense.

 

[tyija1995]

The solutions would be 3; 3; 2; 1; 2; 2 (apologies I got a bit displaced there!) but it should work none the less :D

 

[nparsons75]

Hi, for some reason the result is giving me the count of non blanks in Col A.

IGNORE....

This was because I selected a field for the value as opposed to typing the value in quotes within the formula...

 

[nparsons75]

Hi, The results are not showing as expected, i do have filters on the table, could this be the issue?

 

[tyija1995]

Hey,

What is the range of cells that your data table occupies? Does it start at cell A2?

The filter shouldn't affect the formula I think...

How is the formula being referenced? I know with tables it will change the structure of the formula if you highlight the table column.

You have to fix the first part of the formula to look at column A only (I'd use $A$2:$A$100 notation for this)

 

[nparsons75]

Hi,

This is the formula I am using...

=SUMPRODUCT(--("Desc1"=$A$17:$A$5000),--(""<>E$17:E$5000))

A is the value for the lookup, E is the column I want to count. Physically in E after filtering there are 6 non blank cells. In the cell where the formula is, 2 is the answer returned.