Formula or V Look up

[BWolf]

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

 

[barry houdini]

=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<>""))

 

[BWolf]

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.

 

[barry houdini]

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

 

[BWolf]

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.

 

[barry houdini]

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 =

 

[BWolf]

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


SORRY BOUT CAPS

 

[barry houdini]

PERCENTAGE SOLD OF WHAT WAS LISTED.

Wouldn't that just be

=C2/B2

 

[BWolf]

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

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