# countif?

#### jswaim

##### Board Regular
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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
=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

You know what Im a dork just use Countif and use the & (shift between the cell you want to compare...

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

I keep getting a result of "0". What do you think may be wrong? Thanks

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

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

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

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

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

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

Replies
13
Views
455
Replies
5
Views
600
Replies
2
Views
398
Replies
7
Views
626
Replies
2
Views
308

1,218,690
Messages
6,143,940
Members
450,516
Latest member
shironokuro

### 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