| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
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! |
|
#2
|
|||
|
|||
|
=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! |
|
#3
|
|||
|
|||
|
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! |
|
#4
|
|||
|
|||
|
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! |
|
#5
|
|||
|
|||
|
-- 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! |
|
#6
|
|||
|
|||
|
-- 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! |
|
#7
|
|||
|
|||
|
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! |
|
#8
|
|||
|
|||
|
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! |
|
#9
|
|||
|
|||
|
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) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
| revert formula insertion to old method | Setting up and Configuration of Excel | |||
| Relative Indirect Formula Referencing? | Excel Worksheet Functions | |||
| Help with macro formula and variable | Excel Worksheet Functions | |||
| Cell doesn't show formula result - it shows formula (CTRL + ' doe. | Excel Worksheet Functions | |||
Linear Mode
