Thanks:  0
Likes:  0

# Thread: GOING CRAZY! - Count Functions

1. On 2002-03-24 10:32, Yogi Anand wrote:
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.

2. 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.

3. 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.

4. 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?

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

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

6. 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))

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?

8. On 2002-03-24 11:41, keith wrote:
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,

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

10. 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?

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•