countif?

jswaim

Board Regular
Joined
Sep 27, 2002
Messages
141
Okay, I think I am starting to get somewhere by using the countif. Well, part of the way there. I am using countif(a1:b2"cc15506") to count the model numbers. I still need to count multiple items. Such as: ($ represents separate columns)

Model $ Options $
cc15506 $ ae7 $ np5 $ 1sj $
cc15506 $ ae7 $ ne3 $ 1sj $
cc15506 $ b3r $ np5 $ 1sm $
Anyone have any suggestions on how I can count the number of models with the ae7 and 1sj options? Thank you.
This message was edited by jswaim on 2002-09-28 17:11
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Sincity_Jason

New Member
Joined
Sep 27, 2002
Messages
11
=if(countif(A$:A1,A1&C1&D1)=1,A1,"")

Where At is the Model Number, C1 is the ae7 opton and d1 is the 1sj option...copy formula down (auto fill Works also)

actually after looking again you don't need C1
This message was edited by Sincity_Jason on 2002-09-28 17:45
This message was edited by Sincity_Jason on 2002-09-28 17:47
 

Sincity_Jason

New Member
Joined
Sep 27, 2002
Messages
11
You know what Im a dork just use Countif and use the & (shift :cool: between the cell you want to compare...

=countif(A1:A1,a1&b1&c1) should work...
 

jswaim

Board Regular
Joined
Sep 27, 2002
Messages
141
I keep getting a result of "0". What do you think may be wrong? Thanks
 

jswaim

Board Regular
Joined
Sep 27, 2002
Messages
141

ADVERTISEMENT

Okay, here is the answer to part of my question. I found all the cc15506's with the 1sj option with this formula, but how can I find all cc15506's with a 1sj option and an ae7 option?

SUM(IF(A1:A4="cc15506",IF(B1:D4="1sj",1,0)),B1:D4
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi jswaim:

One of the ways you can do this is by using the SUMPRODUCT function. See the worksheet simulation
Book1
ABCDEF
1ModelOption1Option2Option32
2cc15506ae7np51sj
3cc15506ae7ne31sj
4cc15506b3rnp51sm
Sheet2


Regards!

Yogi
 

jswaim

Board Regular
Joined
Sep 27, 2002
Messages
141

ADVERTISEMENT

Yogi,
Thanks for your reply and that would work if all the vehicles options were in the same order. The problem is that I have hundreds of vehicles with different options for example: cc15506's with ae7, an3, 1sj
and some with ab3, ae7, an3, 1sj, and so on.
I have worked out a formula =COUNT(IF(A1:A4="cc15506",IF(A1:D4="ae7",IF(A1:D4="1sj",1,0))), but for example if I have 3 cc15506's with 3 "ae7" options, but only 2 "1sj" options. It will count the total as 3, when in reality I want to specifically know how many cc15506's I have with the "ae7" and "1sj" options. Which should be 2. Thank you for your response thought I will see if I can figure something out. If you have another suggestion I will appreciate it. Oh yeah I forgot to mention the fact that sometimes the options on each vehicle total 30 or 40, and as mentioned above they are not in an order where i can place them in specific columns. Belive me I would but that would take forever.

Jason
This message was edited by jswaim on 2002-09-28 19:26
This message was edited by jswaim on 2002-09-28 19:30
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
The formula I used will correctly give you the answer as 2. See another worksheet simulation ...
Book1
ABCDEF
1ModelOption1Option2Option32
2cc15506ae7np51sj
3cc15506ae7ne31sj
4cc15506ae7np51sm
Sheet2


If you still have a doubt, please post your sample data -- and tell me what answer you get using my formula -- and what is the answer you expect ... and then let us take it from there.

Regards!

Yogi
 

jswaim

Board Regular
Joined
Sep 27, 2002
Messages
141
Here it is Yogi.
I don't know if it is a problem, but I am using Excel 97. I have the new version at work, which is what I will be using. Anyway, here is the data, $'s show column change.

cc15506 $ ae7,np5,1sj $
cc15506 $ ab3,ae7, np3,1sj $
cc15506 $ ae7,np5,1sm $
ck15506 $ ae7,np5,ke7,1sm $

This is just a shortened version of the option codes, I have to deal with 30 and 40 options per vehicle, and as you can see the ae7 is not always going to be in the second column. Oh yeah, as I mentioned before, if I could place the options all in the same columns I would, but due to time constraints and the amount of data I would have to go through I think it would take days, unless you know a better way.
Appreciate you help.
Jason
This message was edited by jswaim on 2002-09-28 19:57
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
On 2002-09-28 19:52, jswaim wrote:
Here it is Yogi.
I don't know if it is a problem, but I am using Excel 97. I have the new version at work, which is what I will be using. Anyway, here is the data, $'s show column change.

cc15506 $ ae7,np5,1sj $
cc15506 $ ab3,ae7, np3,1sj $
cc15506 $ ae7,np5,1sm $
ck15506 $ ae7,np5,ke7,1sm $

This is just a shortened version of the option codes, I have to deal with 30 and 40 options per vehicle, and as you can see the ae7 is not always going to be in the second column. Oh yeah, as I mentioned before, if I could place the options all in the same columns I would, but due to time constraints and the amount of data I would have to go through I think it would take days, unless you know a better way.
Appreciate you help.
Jason
This message was edited by jswaim on 2002-09-28 19:57

Hi jswaim:

Your data structure is too loose -- you could run into unexpected problems with data being this loosely structured. Anyway

For our specific situation with this loosely structured data, you can use the DCOUNTA function to accomplish what you want. See the worksheet simulation ...
Book1
ABCDEFGHIJK
6
7Model
8cc15506FALSE
9sourcedata
10ModelOption1Option2Option3Option4criteria
11cc15506ae7np51sj2result
12cc15506ab3ae7np31sj
13cc15506ae7np51sm
14ck15506ae7np5ke71sm
Sheet2


Regards!

Yogi
 

Forum statistics

Threads
1,144,208
Messages
5,723,022
Members
422,476
Latest member
beck85

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