Formula or V Look up

BWolf

Board Regular
Joined
Oct 1, 2006
Messages
128
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

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
=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

Board Regular
Joined
Oct 1, 2006
Messages
128
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

Board Regular
Joined
Oct 1, 2006
Messages
128
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.

sheet1.jpg


This is sheet 1-10.

sheet2.jpg


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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

Board Regular
Joined
Oct 1, 2006
Messages
128
FANTASTIC..........WORKS GREAT.....NOW FOR SHEET3 IN COLUMN D TO HAVE A PERCENTAGE SOLD OF WHAT WAS LISTED.


SORRY BOUT CAPS
 

BWolf

Board Regular
Joined
Oct 1, 2006
Messages
128
Great........thank you very much Barry.......same name here. :)

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

Forum statistics

Threads
1,181,371
Messages
5,929,561
Members
436,677
Latest member
CathalP1992

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top