| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complicated SUMPRODUCT
I need a formula to do the following:
If I2:AV2 = "Sht In Form" and I3:AV3 = "YES" If I2:AV2 = "Sheet Present" and I3:AV3 = "YES" What I really need is to count when the first set intersects the second set and I'm not sure how to write that equation. Thanks! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Complicated SUMPRODUCT
Looks like you need 2 different formula's:
=SUMPRODUCT(--(I2:AV2="Sht In Form"),--(I3:AV3="YES")) =SUMPRODUCT(--(I2:AV2="Sheet Present"),--(I3:AV3="YES")) -- Regards, Dave "Barb Reinhardt" wrote: I need a formula to do the following: If I2:AV2 = "Sht In Form" and I3:AV3 = "YES" If I2:AV2 = "Sheet Present" and I3:AV3 = "YES" What I really need is to count when the first set intersects the second set and I'm not sure how to write that equation. Thanks! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Complicated SUMPRODUCT
Hi Barb,
=sumproduct(--(i2:av2="Sht In Form")*(i3:av3="Yes)) =sumproduct(--(i2:av2="Sheet Present")*(i3:av3="Yes)) hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Barb Reinhardt" escreveu: I need a formula to do the following: If I2:AV2 = "Sht In Form" and I3:AV3 = "YES" If I2:AV2 = "Sheet Present" and I3:AV3 = "YES" What I really need is to count when the first set intersects the second set and I'm not sure how to write that equation. Thanks! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Complicated SUMPRODUCT
That gives me the count of one or the other, but I need the count of when the
two intersect. Any other suggestions? Thanks "David Billigmeier" wrote: Looks like you need 2 different formula's: =SUMPRODUCT(--(I2:AV2="Sht In Form"),--(I3:AV3="YES")) =SUMPRODUCT(--(I2:AV2="Sheet Present"),--(I3:AV3="YES")) -- Regards, Dave "Barb Reinhardt" wrote: I need a formula to do the following: If I2:AV2 = "Sht In Form" and I3:AV3 = "YES" If I2:AV2 = "Sheet Present" and I3:AV3 = "YES" What I really need is to count when the first set intersects the second set and I'm not sure how to write that equation. Thanks! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Complicated SUMPRODUCT
What do you mean, you need a count of when I2:AV2="Sht In Form" *AND*
I2:AV2="Sheet Present" *AND* I3:AV3="YES"??? Well I can give you the formula for that right now: =0 There will never be a time when a cell equals "Sht In Form" *AND* "Sheet Present" Do you mean you need a count of when (I2:AV2="Sht In Form" *OR* I2:AV2="Sheet Present") AND I3:AV3="YES"? In this case (by the way this is called the Union of the I2:AV2 ranges, not Intersection): =SUMPRODUCT((I2:AV2="Sht In Form")+(I2:AV2="Sheet Present"),--(I3:AV3="YES")) -- Regards, Dave "Barb Reinhardt" wrote: That gives me the count of one or the other, but I need the count of when the two intersect. Any other suggestions? Thanks "David Billigmeier" wrote: Looks like you need 2 different formula's: =SUMPRODUCT(--(I2:AV2="Sht In Form"),--(I3:AV3="YES")) =SUMPRODUCT(--(I2:AV2="Sheet Present"),--(I3:AV3="YES")) -- Regards, Dave "Barb Reinhardt" wrote: I need a formula to do the following: If I2:AV2 = "Sht In Form" and I3:AV3 = "YES" If I2:AV2 = "Sheet Present" and I3:AV3 = "YES" What I really need is to count when the first set intersects the second set and I'm not sure how to write that equation. Thanks! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Complicated SUMPRODUCT
Not sure, but perhaps a "combination" like this:
=SUMPRODUCT((ISNUMBER(MATCH(I2:AV2,{"Sht In Form","Sheet Present"},0)))*(I3:AV3 = "YES")) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Barb Reinhardt" wrote: That gives me the count of one or the other, but I need the count of when the two intersect. Any other suggestions? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Complicated SUMPRODUCT
I guess I'm not making myself clear.
I want something like this: =SUMPRODUCT(--(IF($I$2:$AV$2="Sht in Form",$I3:$AV3="YES")),--(IF($I2:$AV2="Sheet Present",$I3:$AV3 = "YES"))) But I'm getting a #VALUE error. Suggestions? "David Billigmeier" wrote: What do you mean, you need a count of when I2:AV2="Sht In Form" *AND* I2:AV2="Sheet Present" *AND* I3:AV3="YES"??? Well I can give you the formula for that right now: =0 There will never be a time when a cell equals "Sht In Form" *AND* "Sheet Present" Do you mean you need a count of when (I2:AV2="Sht In Form" *OR* I2:AV2="Sheet Present") AND I3:AV3="YES"? In this case (by the way this is called the Union of the I2:AV2 ranges, not Intersection): =SUMPRODUCT((I2:AV2="Sht In Form")+(I2:AV2="Sheet Present"),--(I3:AV3="YES")) -- Regards, Dave "Barb Reinhardt" wrote: That gives me the count of one or the other, but I need the count of when the two intersect. Any other suggestions? Thanks "David Billigmeier" wrote: Looks like you need 2 different formula's: =SUMPRODUCT(--(I2:AV2="Sht In Form"),--(I3:AV3="YES")) =SUMPRODUCT(--(I2:AV2="Sheet Present"),--(I3:AV3="YES")) -- Regards, Dave "Barb Reinhardt" wrote: I need a formula to do the following: If I2:AV2 = "Sht In Form" and I3:AV3 = "YES" If I2:AV2 = "Sheet Present" and I3:AV3 = "YES" What I really need is to count when the first set intersects the second set and I'm not sure how to write that equation. Thanks! |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Complicated SUMPRODUCT
I'm still not sure what you mean if the formula in my last post didn't cover
what you wanted... i.e.: =SUMPRODUCT((I2:AV2="Sht In Form")+(I2:AV2="Sheet Present"),--(I3:AV3="YES")) Can you post some examples using english, try covering every possible combination of values and what you would like the result to be... e.g.: I2="Sht in Form" AND I3="YES" then return TRUE I2="Sheet Present" AND I3="YES" then return TRUE I2="Sht in Form" AND I3="NO" then return FALSE I2="blah" AND I3="YES" then return FALSE etc. etc.... -- Regards, Dave "Barb Reinhardt" wrote: I guess I'm not making myself clear. I want something like this: =SUMPRODUCT(--(IF($I$2:$AV$2="Sht in Form",$I3:$AV3="YES")),--(IF($I2:$AV2="Sheet Present",$I3:$AV3 = "YES"))) But I'm getting a #VALUE error. Suggestions? "David Billigmeier" wrote: What do you mean, you need a count of when I2:AV2="Sht In Form" *AND* I2:AV2="Sheet Present" *AND* I3:AV3="YES"??? Well I can give you the formula for that right now: =0 There will never be a time when a cell equals "Sht In Form" *AND* "Sheet Present" Do you mean you need a count of when (I2:AV2="Sht In Form" *OR* I2:AV2="Sheet Present") AND I3:AV3="YES"? In this case (by the way this is called the Union of the I2:AV2 ranges, not Intersection): =SUMPRODUCT((I2:AV2="Sht In Form")+(I2:AV2="Sheet Present"),--(I3:AV3="YES")) -- Regards, Dave "Barb Reinhardt" wrote: That gives me the count of one or the other, but I need the count of when the two intersect. Any other suggestions? Thanks "David Billigmeier" wrote: Looks like you need 2 different formula's: =SUMPRODUCT(--(I2:AV2="Sht In Form"),--(I3:AV3="YES")) =SUMPRODUCT(--(I2:AV2="Sheet Present"),--(I3:AV3="YES")) -- Regards, Dave "Barb Reinhardt" wrote: I need a formula to do the following: If I2:AV2 = "Sht In Form" and I3:AV3 = "YES" If I2:AV2 = "Sheet Present" and I3:AV3 = "YES" What I really need is to count when the first set intersects the second set and I'm not sure how to write that equation. Thanks! |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Complicated SUMPRODUCT
David,
Well I can give you the formula for that right now: =0 I totally understand what you said. I am trying to think of a situation that it won't be zero. The only thing that comes to mind is the following. I2:AV2 on two *different* worksheets or workbooks. If we have two sheets, can we say "intersect?" Can we use SUMPRODUCT on two different sheets? Please forgive me if I am not making sense or making it more confusing. But I am curious. Feel free to correct me. I'll stay tuned. This sounds like an interesting puzzle. Epinn "David Billigmeier" wrote in message ... What do you mean, you need a count of when I2:AV2="Sht In Form" *AND* I2:AV2="Sheet Present" *AND* I3:AV3="YES"??? Well I can give you the formula for that right now: =0 There will never be a time when a cell equals "Sht In Form" *AND* "Sheet Present" Do you mean you need a count of when (I2:AV2="Sht In Form" *OR* I2:AV2="Sheet Present") AND I3:AV3="YES"? In this case (by the way this is called the Union of the I2:AV2 ranges, not Intersection): =SUMPRODUCT((I2:AV2="Sht In Form")+(I2:AV2="Sheet Present"),--(I3:AV3="YES")) -- Regards, Dave "Barb Reinhardt" wrote: That gives me the count of one or the other, but I need the count of when the two intersect. Any other suggestions? Thanks "David Billigmeier" wrote: Looks like you need 2 different formula's: =SUMPRODUCT(--(I2:AV2="Sht In Form"),--(I3:AV3="YES")) =SUMPRODUCT(--(I2:AV2="Sheet Present"),--(I3:AV3="YES")) -- Regards, Dave "Barb Reinhardt" wrote: I need a formula to do the following: If I2:AV2 = "Sht In Form" and I3:AV3 = "YES" If I2:AV2 = "Sheet Present" and I3:AV3 = "YES" What I really need is to count when the first set intersects the second set and I'm not sure how to write that equation. Thanks! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Complicated SUMPRODUCT OR SUMIF question? | Excel Discussion (Misc queries) | |||
| complicated sumproduct. | Excel Worksheet Functions | |||
| sumproduct causing memory errors? | Excel Worksheet Functions | |||
| Sumproduct function not working | Excel Worksheet Functions | |||
| adding two sumproduct formulas together | Excel Worksheet Functions | |||
Linear Mode
