countif?

[jswaim]

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

 

[jswaim]

Yogi, I cannot get this one to work. Does it have something to do with me using Excel 97?
=AND(OR(option1="ae7",option2="ae7",option3="ae7",option4="ae7"),OR(option1="1sj",option2="1sj",option3="1sj",option4="1sj"))

This is pretty tricky.
Man, the internet is great, don't know what I'd do without your help. Oh yeah, there is not much that I can think of to better organize the data, only have a half day to put all of this in a report. Like I said some vehicles models come with 5 options and some come with 40, so I can see that is where the problem lies. Am I correct?
Jason
This message was edited by jswaim on 2002-09-28 20:51

 

[Yogi Anand]

On 2002-09-28 20:45, jswaim wrote:
Yogi, I cannot get this one to work. Does it have something to do with me using Excel 97?
=AND(OR(option1="ae7",option2="ae7",option3="ae7",option4="ae7"),OR(option1="1sj",option2="1sj",option3="1sj",option4="1sj"))

This is pretty tricky.
Man, the internet is great, don't know what I'd do without your help. Oh yeah, there is not much that I can think of to better organize the data, only have a half day to put all of this in a report. Like I said some vehicles models come with 5 options and some come with 40, so I can see that is where the problem lies. Am I correct?
Jason
This message was edited by jswaim on 2002-09-28 20:51

Hi jswain:

The DCOUNTA formula that I gave you will be the one to use. I did notice some problems with your data -- your data has some superfluous spaces, in some cases before the entry, in some cases after the entry, and in some cases both before and after the entry. Now here is one of the two things we can do ...

1. If you can clean up your data, the formula I gave you will work and you will get the corrct results

2. I can look into working with your dirty data and build the cleanup operation in the criteria.

Let me know if you are able to clean up the data; in the meantime I am going to look at modifying the criteria to work with the dirty data.

Regards!

Yogi

 

[Yogi Anand]

Hi jmswain:

I have modified the criteria ... using the TRIM function to take care of errant space character in you option values.

So you can leave your data as is -- it is too much trouble to clean it up.

See the worksheet simulation ...

Book1
	A	B	C	D	E	F	G	H	I	J	K
6
7							Model
8							cc15506	FALSE
9											sourcedata
10	Model	Option1	Option2	Option3	Option4						criteria
11	cc15506	ae7	np5	1sj				2			result
12	cc15506	ab3	ae7	np3	1sj
13	cc15506	ae7	np5	1sm
14	ck15506	ae7	np5	ke7	1sm
15
Sheet2

Regards!

Yogi

 

[jswaim]

Yogi,
You have gone beyond my scope of knowledge.
I have never used dcounta. I did try the =AND(OR(trim(option1)="ae7",trim(option2)="ae7",trim(option3)="ae7",trim(option4)="ae7"),OR(trim(option1)="1sj",trim(option2)="1sj",trim(option3)="1sj",trim(option4)="1sj")), how should I put the dcounta together with this? You are really a Excel MVP. Sorry, I do not know how to keep up. I even took Excel in college. Could tell me what steps to take? Remember I am using Excel 97. Although, I do have the newest version at work.
Thank you again, for everything.
Jason

 

[Yogi Anand]

Hi jswaim:

Let us first slow down ... take a deep breath, and look at my worksheet simulation above. You will notice in the top right hand corner, that I am also using Excel 97 on Windows 98.

Then if you look at the worksheet simulation, study it for a minute, you will note my source data is in cells A10:E14; my criteria is in cells G7:H8, and the formula for computed result is in cell H11

Now look at the formula in cell H11 -- that consists of three arguments ...

1. source-data-range,
2. field-number,
3. criteria

and I have spelled those out by color coding those in the simulation.

Pay special attention to the criteria range ...

cell G7 houses Model; cell G8 houses cc15506
cell H7 is blank -- because cell H8 has the following computed criterion ...

'=AND(OR(TRIM(Option1)="ae7",TRIM(Option2)="ae7",TRIM(Option3)="ae7",TRIM(Option4)="ae7"),OR(TRIM(Option1)="1sj",TRIM(Option2)="1sj",TRIM(Option3)="1sj",TRIM(Option4)="1sj"))

now you write the following formula in cell H11:

=DCOUNTA(A10:E14,1,G7:H8)

Does it help?

If your source data has a different range, substitute that for A10:E14 in the above formula;

If your criteria is placed in a different range, substitute that for G7:H8

Well, Good Luck my friend.

Regards!

Yogi
This message was edited by Yogi Anand on 2002-09-28 21:56

 

[jswaim]

Thank you very much
Yogi, it works like a charm.
Jason

 

[Aladin Akyurek]

On 2002-09-28 19:24, jswaim wrote:
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

Let A2:B5 house the sample you provided:

{"cc15506"," ae7,np5,1sj";
"cc15506"," ab3,ae7,np3,1sj";
"cc15506"," ae7,np5,1sm";
"ck15506"," ae7,np5,ke7,1sm"}

In D2:F2 enter the following criteria/conditions:

{"cc15506","ae7","1sj"}

In G2 enter:

=SUMPRODUCT(($A$2:$A$5=D2)*(ISNUMBER(SEARCH(E2,$B$2:$B$5))*ISNUMBER(SEARCH(F2,$B$2:$B$5))))

which will give you the desired count.

See...

aaMultCondCount jswaim.xls
	A	B	C	D	E	F	G	H
1
2	cc15506	ae7,np5,1sj		cc15506	ae7	1sj	2
3	cc15506	ab3,ae7,np3,1sj
4	cc15506	ae7,np5,1sm
5	ck15506	ae7,np5,ke7,1sm
6
Sheet1

 

[Yogi Anand]

Hi jswaim:

If all of your options can be lumped together, the criteria to be used with the DCOUNTA function can be short and simple using the FIND function.

See the worksheet simulation ...

y020929.xls
	A	B	C	D	E	F	G	H	I	J	K
6
7							Model
8							cc15506	TRUE
9	Model	Options									sourcedata
10	cc15506	ae7,np5,1sj									criteria
11	cc15506	ab3,ae7,np3,1sj						2			result
12	cc15506	ae7,np5,1sm
13	ck15506	ae7,np5,ke7,1sm
14
Sheet11

</SPAN>

Regards!

Yogi

 

[jswaim]

Thanks again, Yogi.
This way is even better because the data is actually lumped together. The previous ways I was having to convert text to columns. So you have helped me out even more. Thanks
Jason

 

[Yogi Anand]

On 2002-09-29 10:24, jswaim wrote:
Thanks again, Yogi.
This way is even better because the data is actually lumped together. The previous ways I was having to convert text to columns. So you have helped me out even more. Thanks
Jason

Hi Jason:

Communication is the key -- right Jason!

The better we can communicate the situation to each other, the better we can make things all around.

Regards!

Yogi