Help required with COUNTIF/AND function

Hi
I am desperately trying to count a column of items using 2 pieces of
criteria instead of the one used in 'countif' and have tried using 'countif'
and 'And' function nested. i have a column of number which are item numbers
(column A). Column 'B' uses 2 criteria 'Good', 'Bad'. In column 'C' I am
trying to count all item numbers in the column of the same type which are
'Good'. i.e countif(if A1='item type',"Good"). However, counif only allows
one piec of criteria. Can anyone help please?

Thanks in advance
DAT

=SUMPRODUCT(--(A2:A20="item type"),--(B2:B20="Good"))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"DAT" <datucker@ntlworld.com> wrote in message
news:mbqAg.230$Gv4.195@newsfe4-win.ntli.net...
> Hi
> I am desperately trying to count a column of items using 2 pieces of
> criteria instead of the one used in 'countif' and have tried using
'countif'
> and 'And' function nested. i have a column of number which are item
numbers
> (column A). Column 'B' uses 2 criteria 'Good', 'Bad'. In column 'C' I am
> trying to count all item numbers in the column of the same type which are
> 'Good'. i.e countif(if A1='item type',"Good"). However, counif only
allows
> one piec of criteria. Can anyone help please?
>
> Thanks in advance
> DAT
>
>

hi Dat,

use sumproduct

=sumproduct(--(a1:a1000="good")*(b1:b1000=2))

hth
regards from Brazil
Marcelo

"DAT" escreveu:

> Hi
> I am desperately trying to count a column of items using 2 pieces of
> criteria instead of the one used in 'countif' and have tried using 'countif'
> and 'And' function nested. i have a column of number which are item numbers
> (column A). Column 'B' uses 2 criteria 'Good', 'Bad'. In column 'C' I am
> trying to count all item numbers in the column of the same type which are
> 'Good'. i.e countif(if A1='item type',"Good"). However, counif only allows
> one piec of criteria. Can anyone help please?
>
> Thanks in advance
> DAT
>
>
>

Many thanks that worked a treat!
"Marcelo" <Marcelo@discussions.microsoft.com> wrote in message
news:F5A2FF80-E14B-4E7A-B9BA-13B8FE67DC58@microsoft.com...
> hi Dat,
>
> use sumproduct
>
> =sumproduct(--(a1:a1000="good")*(b1:b1000=2))
>
> hth
> regards from Brazil
> Marcelo
>
> "DAT" escreveu:
>
>> Hi
>> I am desperately trying to count a column of items using 2 pieces of
>> criteria instead of the one used in 'countif' and have tried using
>> 'countif'
>> and 'And' function nested. i have a column of number which are item
>> numbers
>> (column A). Column 'B' uses 2 criteria 'Good', 'Bad'. In column 'C' I
>> am
>> trying to count all item numbers in the column of the same type which are
>> 'Good'. i.e countif(if A1='item type',"Good"). However, counif only
>> allows
>> one piec of criteria. Can anyone help please?
>>
>> Thanks in advance
>> DAT
>>
>>
>>