Formula requiring two different criterias

I need to generate a function that will check two different criterias before
it adds them up.

In one column labeled "CAT" (for category), the values range from "1," "2",
"3", and "4." In the second column labeled "Complete," the values can either
be "YES" or "NO."

If CAT is "1" and if Complete is "Yes," then I want that particular line
item to be added with others with the same criteria. So, if three different
rows contained both criterias, then the value in the cell would be 3.

I hope some one out there can help me with this "simple" function!

Thanks!

=SUMPRODUCT(--(CAT="1"),--(Complete="Yes"))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"MJMP" <[email protected]> wrote in message
news:[email protected]...
> I need to generate a function that will check two different criterias
before
> it adds them up.
>
> In one column labeled "CAT" (for category), the values range from "1,"
"2",
> "3", and "4." In the second column labeled "Complete," the values can
either
> be "YES" or "NO."
>
> If CAT is "1" and if Complete is "Yes," then I want that particular line
> item to be added with others with the same criteria. So, if three
different
> rows contained both criterias, then the value in the cell would be 3.
>
> I hope some one out there can help me with this "simple" function!
>
> Thanks!

Bob,

Thank you for the assistance. I just a have a question to clarify the
function.

What is -- ? Is that the range of cells? Or do I put the range of cells
where "CAT" and "Complete" is shown?

Thanks!

"Bob Phillips" wrote:

> =SUMPRODUCT(--(CAT="1"),--(Complete="Yes"))
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "MJMP" <[email protected]> wrote in message
> news:[email protected]...
> > I need to generate a function that will check two different criterias
> before
> > it adds them up.
> >
> > In one column labeled "CAT" (for category), the values range from "1,"
> "2",
> > "3", and "4." In the second column labeled "Complete," the values can
> either
> > be "YES" or "NO."
> >
> > If CAT is "1" and if Complete is "Yes," then I want that particular line
> > item to be added with others with the same criteria. So, if three
> different
> > rows contained both criterias, then the value in the cell would be 3.
> >
> > I hope some one out there can help me with this "simple" function!
> >
> > Thanks!
>
>
>

the unary minuses turn the TRUE and FALSE into 1s and 0s thus letting you
use the
built in format of SUMPRODUCT, yes, replace CAT and Complete with their
range (needs to be of same size)

--

Regards,

Peo Sjoblom


"MJMP" <[email protected]> wrote in message
news:[email protected]...
> Bob,
>
> Thank you for the assistance. I just a have a question to clarify the
> function.
>
> What is -- ? Is that the range of cells? Or do I put the range of cells
> where "CAT" and "Complete" is shown?
>
> Thanks!
>
> "Bob Phillips" wrote:
>
> > =SUMPRODUCT(--(CAT="1"),--(Complete="Yes"))
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "MJMP" <[email protected]> wrote in message
> > news:[email protected]...
> > > I need to generate a function that will check two different criterias
> > before
> > > it adds them up.
> > >
> > > In one column labeled "CAT" (for category), the values range from "1,"
> > "2",
> > > "3", and "4." In the second column labeled "Complete," the values can
> > either
> > > be "YES" or "NO."
> > >
> > > If CAT is "1" and if Complete is "Yes," then I want that particular
line
> > > item to be added with others with the same criteria. So, if three
> > different
> > > rows contained both criterias, then the value in the cell would be 3.
> > >
> > > I hope some one out there can help me with this "simple" function!
> > >
> > > Thanks!
> >
> >
> >

-- is a double unary to force

--

HTH

RP
(remove nothere from the email address if mailing direct)


"MJMP" <[email protected]> wrote in message
news:[email protected]...
> Bob,
>
> Thank you for the assistance. I just a have a question to clarify the
> function.
>
> What is -- ? Is that the range of cells? Or do I put the range of cells
> where "CAT" and "Complete" is shown?
>
> Thanks!
>
> "Bob Phillips" wrote:
>
> > =SUMPRODUCT(--(CAT="1"),--(Complete="Yes"))
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "MJMP" <[email protected]> wrote in message
> > news:[email protected]...
> > > I need to generate a function that will check two different criterias
> > before
> > > it adds them up.
> > >
> > > In one column labeled "CAT" (for category), the values range from "1,"
> > "2",
> > > "3", and "4." In the second column labeled "Complete," the values can
> > either
> > > be "YES" or "NO."
> > >
> > > If CAT is "1" and if Complete is "Yes," then I want that particular
line
> > > item to be added with others with the same criteria. So, if three
> > different
> > > rows contained both criterias, then the value in the cell would be 3.
> > >
> > > I hope some one out there can help me with this "simple" function!
> > >
> > > Thanks!
> >
> >
> >

-- is a double unary to force the conditions to 1 or 0 so that SP can add
them. CAT and Complete should be changed to the appropriate ranges if they
are not already named ranges.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"MJMP" <[email protected]> wrote in message
news:[email protected]...
> Bob,
>
> Thank you for the assistance. I just a have a question to clarify the
> function.
>
> What is -- ? Is that the range of cells? Or do I put the range of cells
> where "CAT" and "Complete" is shown?
>
> Thanks!
>
> "Bob Phillips" wrote:
>
> > =SUMPRODUCT(--(CAT="1"),--(Complete="Yes"))
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "MJMP" <[email protected]> wrote in message
> > news:[email protected]...
> > > I need to generate a function that will check two different criterias
> > before
> > > it adds them up.
> > >
> > > In one column labeled "CAT" (for category), the values range from "1,"
> > "2",
> > > "3", and "4." In the second column labeled "Complete," the values can
> > either
> > > be "YES" or "NO."
> > >
> > > If CAT is "1" and if Complete is "Yes," then I want that particular
line
> > > item to be added with others with the same criteria. So, if three
> > different
> > > rows contained both criterias, then the value in the cell would be 3.
> > >
> > > I hope some one out there can help me with this "simple" function!
> > >
> > > Thanks!
> >
> >
> >

This is what I have written:

=SUMPRODUCT(--(B1:B220="1"),--(G1:G220="YES"))

Where the B column is CAT and the G column is COMPLETED.

Looking through my data, I know of at least two instances that matches both
criteria. But the cell shows "0."

What do I need to investigate?

"Bob Phillips" wrote:

> -- is a double unary to force the conditions to 1 or 0 so that SP can add
> them. CAT and Complete should be changed to the appropriate ranges if they
> are not already named ranges.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "MJMP" <[email protected]> wrote in message
> news:[email protected]...
> > Bob,
> >
> > Thank you for the assistance. I just a have a question to clarify the
> > function.
> >
> > What is -- ? Is that the range of cells? Or do I put the range of cells
> > where "CAT" and "Complete" is shown?
> >
> > Thanks!
> >
> > "Bob Phillips" wrote:
> >
> > > =SUMPRODUCT(--(CAT="1"),--(Complete="Yes"))
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "MJMP" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > I need to generate a function that will check two different criterias
> > > before
> > > > it adds them up.
> > > >
> > > > In one column labeled "CAT" (for category), the values range from "1,"
> > > "2",
> > > > "3", and "4." In the second column labeled "Complete," the values can
> > > either
> > > > be "YES" or "NO."
> > > >
> > > > If CAT is "1" and if Complete is "Yes," then I want that particular
> line
> > > > item to be added with others with the same criteria. So, if three
> > > different
> > > > rows contained both criterias, then the value in the cell would be 3.
> > > >
> > > > I hope some one out there can help me with this "simple" function!
> > > >
> > > > Thanks!
> > >
> > >
> > >
>
>
>

Perhaps it is numbers, so try

=SUMPRODUCT(--(B1:B220=1),--(G1:G220="YES"))


--

HTH

RP
(remove nothere from the email address if mailing direct)


"MJMP" <[email protected]> wrote in message
news:[email protected]...
> This is what I have written:
>
> =SUMPRODUCT(--(B1:B220="1"),--(G1:G220="YES"))
>
> Where the B column is CAT and the G column is COMPLETED.
>
> Looking through my data, I know of at least two instances that matches
both
> criteria. But the cell shows "0."
>
> What do I need to investigate?
>
> "Bob Phillips" wrote:
>
> > -- is a double unary to force the conditions to 1 or 0 so that SP can
add
> > them. CAT and Complete should be changed to the appropriate ranges if
they
> > are not already named ranges.
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "MJMP" <[email protected]> wrote in message
> > news:[email protected]...
> > > Bob,
> > >
> > > Thank you for the assistance. I just a have a question to clarify the
> > > function.
> > >
> > > What is -- ? Is that the range of cells? Or do I put the range of
cells
> > > where "CAT" and "Complete" is shown?
> > >
> > > Thanks!
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > =SUMPRODUCT(--(CAT="1"),--(Complete="Yes"))
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > RP
> > > > (remove nothere from the email address if mailing direct)
> > > >
> > > >
> > > > "MJMP" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > I need to generate a function that will check two different
criterias
> > > > before
> > > > > it adds them up.
> > > > >
> > > > > In one column labeled "CAT" (for category), the values range from
"1,"
> > > > "2",
> > > > > "3", and "4." In the second column labeled "Complete," the values
can
> > > > either
> > > > > be "YES" or "NO."
> > > > >
> > > > > If CAT is "1" and if Complete is "Yes," then I want that
particular
> > line
> > > > > item to be added with others with the same criteria. So, if three
> > > > different
> > > > > rows contained both criterias, then the value in the cell would be
3.
> > > > >
> > > > > I hope some one out there can help me with this "simple" function!
> > > > >
> > > > > Thanks!
> > > >
> > > >
> > > >
> >
> >
> >

That did it! Thank you again for the help!

"Bob Phillips" wrote:

> Perhaps it is numbers, so try
>
> =SUMPRODUCT(--(B1:B220=1),--(G1:G220="YES"))
>
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)