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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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)