How to COUNT in this case

[vumian]

hi everyone,

i have a database as following:

Date Type
1 a
1 d
1 a
1 c
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 ???

[Excelenator]

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

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

[Mark]

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

[Jim Thomlinson]

=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
>
>

[vumian]

hi man,

it's so good man, thank you very much

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

[Jim Thomlinson]

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

[Jim Thomlinson]

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

[vumian]

hi man,
x-tremely great

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

anyway, your way pro more.
thank a lots

[Jim Jackson]

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

[vumian]

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

[Jim Jackson]

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