I need to create an array formula combined with a countif

I need to only count the "C" in one array, but only if there is a C1 in the
adject cell

Hi!

=SUMPRODUCT(--(A1:A100="C"),--(B1:B100="C1"))

Biff

"Rochelle B" <RochelleB@discussions.microsoft.com> wrote in message
news:C952594A-CEA0-4A4C-BBE4-C5343D80D0AB@microsoft.com...
>I need to only count the "C" in one array, but only if there is a C1 in the
> adject cell

I am not understanding the (--, please forgive me if this is a stupid
question, it's late and I am tired and need to finish this for a report in
the morning.

Thanks

"Biff" wrote:

> Hi!
>
> =SUMPRODUCT(--(A1:A100="C"),--(B1:B100="C1"))
>
> Biff
>
> "Rochelle B" <RochelleB@discussions.microsoft.com> wrote in message
> news:C952594A-CEA0-4A4C-BBE4-C5343D80D0AB@microsoft.com...
> >I need to only count the "C" in one array, but only if there is a C1 in the
> > adject cell
>
>
>

Hi!

Each element of the arrays:

(A1:A100="C")
(B1:B100="C1")

return either TRUE or FALSE

"--" converts these to 1's and 0's which Sumproduct can then process.

Biff

"Rochelle B" <RochelleB@discussions.microsoft.com> wrote in message
news:E002D0AB-FCF1-46EF-B360-ECB2EDB0830B@microsoft.com...
>I am not understanding the (--, please forgive me if this is a stupid
> question, it's late and I am tired and need to finish this for a report in
> the morning.
>
> Thanks
>
> "Biff" wrote:
>
>> Hi!
>>
>> =SUMPRODUCT(--(A1:A100="C"),--(B1:B100="C1"))
>>
>> Biff
>>
>> "Rochelle B" <RochelleB@discussions.microsoft.com> wrote in message
>> news:C952594A-CEA0-4A4C-BBE4-C5343D80D0AB@microsoft.com...
>> >I need to only count the "C" in one array, but only if there is a C1 in
>> >the
>> > adject cell
>>
>>
>>

I will give it a try, I just posted this same question but a lot more
detailed, maybe it will make more sense. I am not familiar with this type of
formula, so I am not sure it will work. Please read my other posting.

"Biff" wrote:

> Hi!
>
> Each element of the arrays:
>
> (A1:A100="C")
> (B1:B100="C1")
>
> return either TRUE or FALSE
>
> "--" converts these to 1's and 0's which Sumproduct can then process.
>
> Biff
>
> "Rochelle B" <RochelleB@discussions.microsoft.com> wrote in message
> news:E002D0AB-FCF1-46EF-B360-ECB2EDB0830B@microsoft.com...
> >I am not understanding the (--, please forgive me if this is a stupid
> > question, it's late and I am tired and need to finish this for a report in
> > the morning.
> >
> > Thanks
> >
> > "Biff" wrote:
> >
> >> Hi!
> >>
> >> =SUMPRODUCT(--(A1:A100="C"),--(B1:B100="C1"))
> >>
> >> Biff
> >>
> >> "Rochelle B" <RochelleB@discussions.microsoft.com> wrote in message
> >> news:C952594A-CEA0-4A4C-BBE4-C5343D80D0AB@microsoft.com...
> >> >I need to only count the "C" in one array, but only if there is a C1 in
> >> >the
> >> > adject cell
> >>
> >>
> >>
>
>
>

You are the bomb! IT WORKS!!!! I have never known anything about t he '--'
before and I am not clear how it works, but it did - perfectly!

"Biff" wrote:

> Hi!
>
> Each element of the arrays:
>
> (A1:A100="C")
> (B1:B100="C1")
>
> return either TRUE or FALSE
>
> "--" converts these to 1's and 0's which Sumproduct can then process.
>
> Biff
>
> "Rochelle B" <RochelleB@discussions.microsoft.com> wrote in message
> news:E002D0AB-FCF1-46EF-B360-ECB2EDB0830B@microsoft.com...
> >I am not understanding the (--, please forgive me if this is a stupid
> > question, it's late and I am tired and need to finish this for a report in
> > the morning.
> >
> > Thanks
> >
> > "Biff" wrote:
> >
> >> Hi!
> >>
> >> =SUMPRODUCT(--(A1:A100="C"),--(B1:B100="C1"))
> >>
> >> Biff
> >>
> >> "Rochelle B" <RochelleB@discussions.microsoft.com> wrote in message
> >> news:C952594A-CEA0-4A4C-BBE4-C5343D80D0AB@microsoft.com...
> >> >I need to only count the "C" in one array, but only if there is a C1 in
> >> >the
> >> > adject cell
> >>
> >>
> >>
>
>
>