Counting Function

[Mythran]

I have the following in a workbook:

Sheet1 - 3 Columns

1/1/2006 1 A
1/2/2006 2 B
1/1/2006 3 C
1/2/2006 4 A
1/2/2006 5 B


Sheet2 - 1 Column, 2 Rows

1/1/2006
<insert function(s) here>

Using function(s), without VBA, is it possible to get the total number of
rows for the date specified in Sheet2!A1 that also have a text value which
is 'A'?

The count, dcount, and countif functions allow for a single condition (or
more than 1 condition), for a single set of values (range)...in my case,
there are 2 sets of values to check for the count...

I hope it's possible

Thanks,
Mythran

 

[Don Guillett]

try sumproduct - to count
=sumproduct((a2:a22=a1)*(c2:c22="A"))
to sum col b
=sumproduct((a2:a22=a1)*(c2:c22="A")*b2:b22)

 

[Mythran]

I don't see that it helps. Maybe I wasn't clear on what i am trying to
accomplish so I shall try again and give results of what I want:

Slightly diff example:

01/01/2006 1 A
01/02/2006 2 B
01/01/2006 3 C
01/02/2006 4 A
01/02/2006 5 B
01/01/2006 6 A

1.) In cell A10 I would put a date, such as "01/01/2006".

2.) In cell A11, I would like to show the total number of rows that match
the criteria of "date value in column A is equal to the date entered in cell
A10" and "text value in column C is equal to the text value 'A'".

3.) In cell A12, I would like to show the sum of the values in column B for
those rows that match the same criteria for the count above.


When I enter in the value of "01/01/2006" into cell A10, the results for
cell A11 would be "2" and cell A12 would be "7" [only the first and last
rows match the specified criteria].

When I enter in the value of "01/02/2006" into cell A10, the results for
cell A11 would be "1" and cell A12 would be "4" [only the fourth row matches
the specified criteria].

These are the requirements (very similar to what I'm trying to accomplish).
Is it possible without VBA? If so, how can it be done? I hope I was clear
this time

Thanks,
Mythran

 

[Don Guillett]

Did you TRY what I posted????

 

[Mythran]

Yes, I did, and both results returned 0.

Mythran

Did you TRY what I posted????

--
Don Guillett
SalesAid Software
(e-mail address removed)

I don't see that it helps. Maybe I wasn't clear on what i am trying to
accomplish so I shall try again and give results of what I want:

Slightly diff example:

01/01/2006 1 A
01/02/2006 2 B
01/01/2006 3 C
01/02/2006 4 A
01/02/2006 5 B
01/01/2006 6 A

1.) In cell A10 I would put a date, such as "01/01/2006".

2.) In cell A11, I would like to show the total number of rows that match
the criteria of "date value in column A is equal to the date entered in
cell A10" and "text value in column C is equal to the text value 'A'".

3.) In cell A12, I would like to show the sum of the values in column B
for those rows that match the same criteria for the count above.


When I enter in the value of "01/01/2006" into cell A10, the results for
cell A11 would be "2" and cell A12 would be "7" [only the first and last
rows match the specified criteria].

When I enter in the value of "01/02/2006" into cell A10, the results for
cell A11 would be "1" and cell A12 would be "4" [only the fourth row
matches the specified criteria].

These are the requirements (very similar to what I'm trying to
accomplish). Is it possible without VBA? If so, how can it be done? I
hope I was clear this time

Thanks,
Mythran

 

[Don Guillett]

Do you have your dates properly formatted as dates?
If so, feel free to send me the workbook for a look. Use the address below

--
Don Guillett
SalesAid Software
(e-mail address removed)

Yes, I did, and both results returned 0.

Mythran

Did you TRY what I posted????

--
Don Guillett
SalesAid Software
(e-mail address removed)

I don't see that it helps. Maybe I wasn't clear on what i am trying to
accomplish so I shall try again and give results of what I want:

Slightly diff example:

01/01/2006 1 A
01/02/2006 2 B
01/01/2006 3 C
01/02/2006 4 A
01/02/2006 5 B
01/01/2006 6 A

1.) In cell A10 I would put a date, such as "01/01/2006".

2.) In cell A11, I would like to show the total number of rows that
match the criteria of "date value in column A is equal to the date
entered in cell A10" and "text value in column C is equal to the text
value 'A'".

3.) In cell A12, I would like to show the sum of the values in column B
for those rows that match the same criteria for the count above.


When I enter in the value of "01/01/2006" into cell A10, the results for
cell A11 would be "2" and cell A12 would be "7" [only the first and last
rows match the specified criteria].

When I enter in the value of "01/02/2006" into cell A10, the results for
cell A11 would be "1" and cell A12 would be "4" [only the fourth row
matches the specified criteria].

These are the requirements (very similar to what I'm trying to
accomplish). Is it possible without VBA? If so, how can it be done? I
hope I was clear this time

Thanks,
Mythran