hi everyone,

i have a database as following:

Date Type
1 a
1 d
1 a
2 a
2 b
3 c
3 a
3 d
..
31

Result Here
Date Count
1 ? (result is 3)
2 ? (Result is 1)
..
31

I wanna count, how many a and d are there in Date 1 ??

--
vumia
-----------------------------------------------------------------------
vumian's Profile: http://www.excelforum.com/member.php...fo&userid=3649
View this thread: http://www.excelforum.com/showthread.php?threadid=56834

Assuming your data is in Cells A1:B9 you can use this formula

=SUMPRODUCT((A1:A9=1)*((B1:B9="a")+(B1:B9="d"))

--
Excelenato

-----------------------------------------------------------------------
Excelenator's Profile: http://www.excelforum.com/member.php...fo&userid=3676
View this thread: http://www.excelforum.com/showthread.php?threadid=56834

I dont know of a way to do this with a built in Excel Function. If you
want I could build you a custom one. I would just need you to answer a
few questions so I could do Exactly what you wanted. Lemme know...

=SUMPRODUCT((D1=$A$2:$A$100)*(($B$2:$B$100="a")+($ B$2:$B$100="d")))

Where The number 1 is in D1, 2 is in D2, ... and your database is in A2:B100
--
HTH...

Jim Thomlinson


"vumian" wrote:


hi everyone,

i have a database as following:

Date Type
1 a
1 d
1 a
2 a
2 b
3 c
3 a
3 d
..
31

Result Here
Date Count
1 ? (result is 3)
2 ? (Result is 1)
..
31

I wanna count, how many a and d are there in Date 1 ???


--
vumian
------------------------------------------------------------------------
vumian's Profile: http://www.excelforum.com/member.php...o&userid=36494
View this thread: http://www.excelforum.com/showthread...hreadid=568344

hi man,

it's so good man, thank you very much

i wanna understand more about fx SUMPRODUCT, where can i find it ?


--
vumian
------------------------------------------------------------------------
vumian's Profile: http://www.excelforum.com/member.php...o&userid=36494
View this thread: http://www.excelforum.com/showthread...hreadid=568344

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
--
HTH...

Jim Thomlinson


"vumian" wrote:


hi man,

it's so good man, thank you very much

i wanna understand more about fx SUMPRODUCT, where can i find it ?


--
vumian
------------------------------------------------------------------------
vumian's Profile: http://www.excelforum.com/member.php...o&userid=36494
View this thread: http://www.excelforum.com/showthread...hreadid=568344

hi Jim Thomlinson,

everyone here is nice ,
oke, there is a small thing,
how about i can count the rest thing, same example above ?

result

1 ? (result is 1)
2 ? (1 too)

i do not wanna use fx + many times, coz it makes fomula long

thank you for help one more

--
vumia
-----------------------------------------------------------------------
vumian's Profile: http://www.excelforum.com/member.php...fo&userid=3649
View this thread: http://www.excelforum.com/showthread.php?threadid=56834

So all of the items that are not a and not d

=SUMPRODUCT((D1=$A$2:$A$100)*($B$2:$B$100<"a")*($ B$2:$B$100<"d"))

--
HTH...

Jim Thomlinson


"vumian" wrote:


hi Jim Thomlinson,

everyone here is nice ,
oke, there is a small thing,
how about i can count the rest thing, same example above ?

result

1 ? (result is 1)
2 ? (1 too)

i do not wanna use fx + many times, coz it makes fomula long

thank you for help one more.


--
vumian
------------------------------------------------------------------------
vumian's Profile: http://www.excelforum.com/member.php...o&userid=36494
View this thread: http://www.excelforum.com/showthread...hreadid=568344

hi man,
x-tremely great

i aslo other way, i use countif(range,criteria)-result above

anyway, your way pro more.
thank a lots


--
vumian
------------------------------------------------------------------------
vumian's Profile: http://www.excelforum.com/member.php...o&userid=36494
View this thread: http://www.excelforum.com/showthread...hreadid=568344

If you just want to use formulae in the Results cells, this will work. Be
sure to press "ctrl/alt/Enter" since this is an array formula.

=IF(B2:B9 = "a",COUNTIF(A2:A9,"1"),0)

Just change the letters and numbers for each respective cell.
--
Best wishes,

Jim


"vumian" wrote:


hi everyone,

i have a database as following:

Date Type
1 a
1 d
1 a
2 a
2 b
3 c
3 a
3 d
..
31

Result Here
Date Count
1 ? (result is 3)
2 ? (Result is 1)
..
31

I wanna count, how many a and d are there in Date 1 ???


--
vumian
------------------------------------------------------------------------
vumian's Profile: http://www.excelforum.com/member.php...o&userid=36494
View this thread: http://www.excelforum.com/showthread...hreadid=568344

hi Jim Jackson,

thanks for your code
but it do not work for me, i do not know why ?

and i Press Ctrl Alt Enter, Nothing happened man, why ?

and what mean once pressing Ctrl Alt Enter ?

thanks in advance for your explanation


--
vumian
------------------------------------------------------------------------
vumian's Profile: http://www.excelforum.com/member.php...o&userid=36494
View this thread: http://www.excelforum.com/showthread...hreadid=568344

I should have said that the "Ctrl" and "Alt" buttons need to be down before
pressing "Enter." This may be why it is not working. Also, be sure the
correct cells are referenced in your formula.
--
Best wishes,

Jim


"vumian" wrote:


hi Jim Jackson,

thanks for your code
but it do not work for me, i do not know why ?

and i Press Ctrl Alt Enter, Nothing happened man, why ?

and what mean once pressing Ctrl Alt Enter ?

thanks in advance for your explanation


--
vumian
------------------------------------------------------------------------
vumian's Profile: http://www.excelforum.com/member.php...o&userid=36494
View this thread: http://www.excelforum.com/showthread...hreadid=568344