count double occurences

[Henderson]

hello
i am trying to create a worksheet that will count how many times one
criteria is met in one column and another is met in a second column

the point is to count the times students have late assignments in the
various different subjects

example:

Adam History
Adam Math
Bob History
Adam Math

how many times did Adam have a late assignment in Math?

there a ppeared to be formula in Help that i could use but I couldn't get it
to work - not even by recreating their example
=COUNT(IF((A2:A11="Adam")*(B2:B11="Math"),D2:D11))
(i don't know why they needed a D range here)

[Peo Sjoblom]

=SUMPRODUCT(--(A2:A11="Adam"),--(B2:B11="Math"))

but it's better to use

=SUMPRODUCT(--(A2:A11=C2),--(B2:B11=D2))

where you would put the criteria in the cells thus you don't have to edit
the formula to change student/subject

--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com



"Henderson" <[email protected]> wrote in message
news:[email protected]...
> hello
> i am trying to create a worksheet that will count how many times one
> criteria is met in one column and another is met in a second column
>
> the point is to count the times students have late assignments in the
> various different subjects
>
> example:
>
> Adam History
> Adam Math
> Bob History
> Adam Math
>
> how many times did Adam have a late assignment in Math?
>
> there a ppeared to be formula in Help that i could use but I couldn't get
> it
> to work - not even by recreating their example
> =COUNT(IF((A2:A11="Adam")*(B2:B11="Math"),D2:D11))
> (i don't know why they needed a D range here)

[Ashish Mathur]

Hi,

Although sumproduct is the best solution, you may also want to try this
array formula (Ctrl+Shift+Enter)

=sum(if((A2:A11="Adam")*(B2:B11="Math"),1,0))

Regards,

Ashish Mathur

"Henderson" wrote:

> hello
> i am trying to create a worksheet that will count how many times one
> criteria is met in one column and another is met in a second column
>
> the point is to count the times students have late assignments in the
> various different subjects
>
> example:
>
> Adam History
> Adam Math
> Bob History
> Adam Math
>
> how many times did Adam have a late assignment in Math?
>
> there a ppeared to be formula in Help that i could use but I couldn't get it
> to work - not even by recreating their example
> =COUNT(IF((A2:A11="Adam")*(B2:B11="Math"),D2:D11))
> (i don't know why they needed a D range here)

[Henderson]

this seems to being do the trick. thank you. i am amazed people would do this

"Peo Sjoblom" wrote:

> =SUMPRODUCT(--(A2:A11="Adam"),--(B2:B11="Math"))
>
> but it's better to use
>
> =SUMPRODUCT(--(A2:A11=C2),--(B2:B11=D2))
>
> where you would put the criteria in the cells thus you don't have to edit
> the formula to change student/subject
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> http://nwexcelsolutions.com
>
>
>
> "Henderson" <[email protected]> wrote in message
> news:[email protected]...
> > hello
> > i am trying to create a worksheet that will count how many times one
> > criteria is met in one column and another is met in a second column
> >
> > the point is to count the times students have late assignments in the
> > various different subjects
> >
> > example:
> >
> > Adam History
> > Adam Math
> > Bob History
> > Adam Math
> >
> > how many times did Adam have a late assignment in Math?
> >
> > there a ppeared to be formula in Help that i could use but I couldn't get
> > it
> > to work - not even by recreating their example
> > =COUNT(IF((A2:A11="Adam")*(B2:B11="Math"),D2:D11))
> > (i don't know why they needed a D range here)
>
>
>

[Henderson]

thanks for the thought. i am excited to get this working.

"Ashish Mathur" wrote:

> Hi,
>
> Although sumproduct is the best solution, you may also want to try this
> array formula (Ctrl+Shift+Enter)
>
> =sum(if((A2:A11="Adam")*(B2:B11="Math"),1,0))
>
> Regards,
>
> Ashish Mathur
>
> "Henderson" wrote:
>
> > hello
> > i am trying to create a worksheet that will count how many times one
> > criteria is met in one column and another is met in a second column
> >
> > the point is to count the times students have late assignments in the
> > various different subjects
> >
> > example:
> >
> > Adam History
> > Adam Math
> > Bob History
> > Adam Math
> >
> > how many times did Adam have a late assignment in Math?
> >
> > there a ppeared to be formula in Help that i could use but I couldn't get it
> > to work - not even by recreating their example
> > =COUNT(IF((A2:A11="Adam")*(B2:B11="Math"),D2:D11))
> > (i don't know why they needed a D range here)