GOING CRAZY! - Count Functions

[keith]

Example Data:
Date Column | Item1 Col | Item2 Col
2002/03/23 | 2 | 5
2002/03/25 | 5 | 6
2002/03/23 | 2 | 5
many more rows of similar...

Example Problem: - I need to count the number of times a "2" is listed in the "item 1" column when the date in "date column" is "2002/03/23" for the same row etc.. (2 would be above result)

I am pretty sure this is beyond simple COUNTIF and I can't seem to make DCOUNT work either. A DCOUNTIF would be perfect but it doesn't exist that I am aware of.. VBA maybe? or maybe I am missing something on COUNTIF or DCOUNT? - Please help - thanks in advance..

 

[trillicomm]

On 2002-03-24 10:32, Yogi Anand wrote:
Hi Aladin:
You are right (I noticed it after my posting)-- my point was Keith said DCOUNT did not work for him ( he wished there would be something like a DCOUNTIF function) so I was wondering what he did that made the function not work (perhaps he may have included the split vertical bars as part of his data ... just guessing!) T H A N K S!
This message was edited by Yogi Anand on 2002-03-24 10:33

How do you not include the split vertical bars in the database?

I duplicated but found 3 not 2.

 

[Aladin Akyurek]

On 2002-03-24 10:41, keith wrote:
yea - I got the DCOUNT to work after Dave posted, but in the total scope of the project I think the SUMPRODUCT works better as I need to scan multiple "item" columns for a specified # based on date. For example I need to return how many total "2"'s there are in Item1 AND Item2 and so on for a specific date. SUMPRODUCT seems to do this for me.. Thanks for responding though.. the formula I am using applicable to the initial example would be - =SUMPRODUCT((A2:A6=E2)*(B2:C6=F2)) (notice that b2:c6 would be both Item1 and Item2 data...) and I have 48 items to extend the range to cover..

Keith,

I'm confused. AND'ing the way you do would require that Item1 as well as Item2 must meet the condition of being 2. So when Item1 is 2 but Item2 not, the count will not include the target row. Extending the criteria for DCOUNT will behave the same.

Aladin

 

[keith]

the split vertical bars or "|" i put in the initial post were just for the example. I just used them to demonstrate column breaks in the post.

 

[keith]

well it seems to work (I hope its not just working for some wierd coincidence).. my end goal is to count everytime a "2" shows up in ANY ITEM COLUMN where a specific date is met in the DATA COLUMN. Keep in mind I am not counting rows but cells that contain "2". So if the date matches and ITEM 1=2 and ITEM2=2 then the formula result should be "2". If item1=1 and item2=2 the result should be "1" and vice versa.. Am I all messed up?

 

[keith]

http://www.strategiccs.com/sumproduct.xls

this is the example i am working with if it helps..

 

[Aladin Akyurek]

On 2002-03-24 11:05, keith wrote:
well it seems to work (I hope its not just working for some wierd coincidence).. my end goal is to count everytime a "2" shows up in ANY ITEM COLUMN where a specific date is met in the DATA COLUMN. Keep in mind I am not counting rows but cells that contain "2". So if the date matches and ITEM 1=2 and ITEM2=2 then the formula result should be "2". If item1=1 and item2=2 the result should be "1" and vice versa.. Am I all messed up?

Lets say that A1:C4 houses the following sample:

{"Date","Item1","Item2";
37338,2,2;
37340,5,2;
37338,2,8}

and F2:G2 houses

{37338,2}

According to your current specs, we should get 2 as result.

[1]

=SUMPRODUCT((A2:A4=F2)*(A2:C4=2))

will produce 3.

[2], which will I'm afraid discourage you,

=SUMPRODUCT((A2:A4=F2)*((B2:B4=G2)+(C2:C4=G2)))-SUMPRODUCT((A2:A4=F2)*(B2:B4=G2)*(C2:C4=G2))

will compute the right answer.

Aladin

 

[keith]

(Your Example)
Lets say that A1:C4 houses the following sample:

{"Date","Item1","Item2";
37338,2,2;
37340,5,2;
37338,2,8}

and F2:G2 houses

{37338,2}

According to your current specs, we should get 2 as result.

- Let me break in here.. according to my needs I shoud get "3" as a result not "2" because I need to count EVERY CELL that houses a "2" when the date is met.. so in the above example "2" is listed 3 total times when 37338 is in the date field.. see what I mean?

 

[Aladin Akyurek]

On 2002-03-24 11:41, keith wrote:
(Your Example)
Lets say that A1:C4 houses the following sample:

{"Date","Item1","Item2";
37338,2,2;
37340,5,2;
37338,2,8}

and F2:G2 houses

{37338,2}

According to your current specs, we should get 2 as result.

- Let me break in here.. according to my needs I shoud get "3" as a result not "2" because I need to count EVERY CELL that houses a "2" when the date is met.. so in the above example "2" is listed 3 total times when 37338 is in the date field.. see what I mean?

Keith,

I now hear you...
The wording made me a bit nervous at first... But, at the end, you're right in your judgement about the use you've put SUMPRODUCT to.

Regards,

Aladin

 

[keith]

Sorry - had to step away for a few.. is there any problem with SUMPRODUCT when the data is on a different sheet?

 

[Aladin Akyurek]

On 2002-03-24 14:51, keith wrote:
Sorry - had to step away for a few.. is there any problem with SUMPRODUCT when the data is on a different sheet?

No. Did you encounter a specific problem?

Aladin