# Formula or V Look up

#### BWolf

##### Board Regular
I have a sheet where column C list all my different dealers. In column K it list the buyers and if not sold, I put a NS. What formula would be used to calculate for each Dealer in C how many sold items in K. In other words, I would like to know how many times a dealer in C listed and how many times there is a sold name in K other than a NS?

TY

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
=Perhaps

=SUMPRODUCT(--(C2:C100="dealer1"),--(K2:K100<>"NS"))

If you might have dealer names in C and blanks in K then use

=SUMPRODUCT(--(C2:C100="dealer1"),--(K2:K100<>"NS"),--(K2:K100<>""))

Hmmmmm....confused. The sheet that contains all this date is named 1-10. Om this sheet I have column containing names of dealers. In column K I have listed either a sold dollar amount or a NS. On Sheet3 I have 3 columns made. Dealer, Listed, Sold. I want to be able to have those 3 columns tell me how many times a dealer(LOCH is an example of 1 of the dealers) listed their name in C and how many times a dollar amount shows in column K which means is was sold.
Guess I need to know where the formulas go also.

OK,

If you have a specific dealer (e.g. "LOCH") in A2 on your summary sheet then in the sold column (C2?) try

=SUMPRODUCT(--('1-10'!C\$2:C\$1000=A2),--ISNUMBER('1-10'!K\$2:K\$1000))

which you can copy down the column to get figures for other dealers

Didn't work..... The only thing that happened when I pasted your formula was the formula shows up in the cell C2 on Sheet3.

I posted 2 images.

This is sheet 1-10.

This is sheet3.

So my goal is to have on sheet3, which I will re-name Totals or something like that, is to have the total sheet read how many times the dealer listed their name. You can see DES-B, then ELKINS and CHI, ect. Then in K if there is a name, not a NS means it was sold.

On the other sheet I want the totals. I will list all the dealers I have in A and in B2 it will show me how many times their name is listed and in C2 it will show how many times their is a name other than a NS.

Hope that helps.

Hello BWolf,

I thought you said in your last post that there were dollar amounts in column K, hence I changed my suggested formula slightly. Given your screenshots try these formulas in sheet3 B2 and C2

=COUNTIF('1-10'!C\$2:C\$1000,A2)

=SUMPRODUCT(--('1-10'!C\$2:C\$1000=A2),--('1-10'!K\$2:K\$1000<>"NS"),--('1-10'!K\$2:K\$1000<>""))

If you only see the actual formula in the cell then either you haven't entered it correctly (e.g. no = sign) or your cells are formatted as text. To solve the latter problem you cannot reformat the cells, that doesn't work, but you can try doing an

Edit > Replace and replacing = with =

FANTASTIC..........WORKS GREAT.....NOW FOR SHEET3 IN COLUMN D TO HAVE A PERCENTAGE SOLD OF WHAT WAS LISTED.

SORRY BOUT CAPS

Great........thank you very much Barry.......same name here.

Now I will chart it also......

Replies
10
Views
662
Replies
3
Views
324
Replies
0
Views
126
Replies
10
Views
376
Replies
2
Views
212

1,217,682
Messages
6,137,959
Members
450,101
Latest member
Mrupe86

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back