Formula requiring two different criterias

[Guest]

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 Phillips]

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

--

HTH

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

 

[Guest]

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!

 

[Peo Sjoblom]

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)

 

[Bob Phillips]

-- is a double unary to force

--

HTH

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

 

[Bob Phillips]

-- 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)

 

[Guest]

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]

Perhaps it is numbers, so try

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


--

HTH

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

 

[Guest]

That did it! Thank you again for the help!