| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMPRODUCT
I need to create a formula where 2 column criteria must match--Columns A&E
(See Below) Column A may only have partial information that matches (i.e. it may show "Netsmart USA" one time and just "Netsmart"the next time--but it will always show at least "Netsmart"). Column E will always be the same (i.e., "53000.5300700001.00300.228200.20070.56995.FMS ") __________________________________________________ ______________ FORMULA =SUMPRODUCT(--ISNUMBER(SEARCH("NETSMART",'07-08 Pd. in 07-08-22820'!$A$4:$A$1000('07-08 Pd. in 07-08-22820'!$E$4:$E$1000="53000.5300700001.0030.228200. 20070.56995.FMS")))--('07-08 Pd. in 07-08-22820'!$B$4:$B$1000)) __________________________________________________ _______________ If Columns A&E BOTH match, I want the total of Column B figures for the match ONLY. Otherwise the result should be zero. The result I keep receiveing is DOUBLE the amount of the ENTIRE Column B not just the matched criteria. HELP!! -- Thank you for your help MO Albany, NY |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SUMPRODUCT
It looks to me like it should be:
=SUMPRODUCT(--ISNUMBER(SEARCH("NETSMART",'07-08 Pd. in 07-08-22820'!$A$4:$A$1000)),--('07-08 Pd. in 07-08-22820'!$E$4:$E$1000="53000.5300700001.0030.228200. 20070.56995.FMS"),'07-08 Pd. in 07-08-22820'!$B$4:$B$1000) -- Regards, Tom Ogilvy "MO" wrote: I need to create a formula where 2 column criteria must match--Columns A&E (See Below) Column A may only have partial information that matches (i.e. it may show "Netsmart USA" one time and just "Netsmart"the next time--but it will always show at least "Netsmart"). Column E will always be the same (i.e., "53000.5300700001.00300.228200.20070.56995.FMS ") __________________________________________________ ______________ FORMULA =SUMPRODUCT(--ISNUMBER(SEARCH("NETSMART",'07-08 Pd. in 07-08-22820'!$A$4:$A$1000('07-08 Pd. in 07-08-22820'!$E$4:$E$1000="53000.5300700001.0030.228200. 20070.56995.FMS")))--('07-08 Pd. in 07-08-22820'!$B$4:$B$1000)) __________________________________________________ _______________ If Columns A&E BOTH match, I want the total of Column B figures for the match ONLY. Otherwise the result should be zero. The result I keep receiveing is DOUBLE the amount of the ENTIRE Column B not just the matched criteria. HELP!! -- Thank you for your help MO Albany, NY |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SUMPRODUCT
There is a missing comma
=SUMPRODUCT(--ISNUMBER(SEARCH("NETSMART",'07-08 Pd. in 07-08-22820'!$A$4:$A$1000('07-08 Pd. in 07-08-22820'!$E$4:$E$1000="53000.5300700001.0030.228200. 20070.56995.FMS"))),--('07-08 Pd. in 07-08-22820'!$B$4:$B$1000)) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "MO" wrote in message ... I need to create a formula where 2 column criteria must match--Columns A&E (See Below) Column A may only have partial information that matches (i.e. it may show "Netsmart USA" one time and just "Netsmart"the next time--but it will always show at least "Netsmart"). Column E will always be the same (i.e., "53000.5300700001.00300.228200.20070.56995.FMS ") __________________________________________________ ______________ FORMULA =SUMPRODUCT(--ISNUMBER(SEARCH("NETSMART",'07-08 Pd. in 07-08-22820'!$A$4:$A$1000('07-08 Pd. in 07-08-22820'!$E$4:$E$1000="53000.5300700001.0030.228200. 20070.56995.FMS")))--('07-08 Pd. in 07-08-22820'!$B$4:$B$1000)) __________________________________________________ _______________ If Columns A&E BOTH match, I want the total of Column B figures for the match ONLY. Otherwise the result should be zero. The result I keep receiveing is DOUBLE the amount of the ENTIRE Column B not just the matched criteria. HELP!! -- Thank you for your help MO Albany, NY |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SUMPRODUCT
I get 0 as a total, which is incorrect.
-- Thank you for your help MO Albany, NY "Tom Ogilvy" wrote: It looks to me like it should be: =SUMPRODUCT(--ISNUMBER(SEARCH("NETSMART",'07-08 Pd. in 07-08-22820'!$A$4:$A$1000)),--('07-08 Pd. in 07-08-22820'!$E$4:$E$1000="53000.5300700001.0030.228200. 20070.56995.FMS"),'07-08 Pd. in 07-08-22820'!$B$4:$B$1000) -- Regards, Tom Ogilvy "MO" wrote: I need to create a formula where 2 column criteria must match--Columns A&E (See Below) Column A may only have partial information that matches (i.e. it may show "Netsmart USA" one time and just "Netsmart"the next time--but it will always show at least "Netsmart"). Column E will always be the same (i.e., "53000.5300700001.00300.228200.20070.56995.FMS ") __________________________________________________ ______________ FORMULA =SUMPRODUCT(--ISNUMBER(SEARCH("NETSMART",'07-08 Pd. in 07-08-22820'!$A$4:$A$1000('07-08 Pd. in 07-08-22820'!$E$4:$E$1000="53000.5300700001.0030.228200. 20070.56995.FMS")))--('07-08 Pd. in 07-08-22820'!$B$4:$B$1000)) __________________________________________________ _______________ If Columns A&E BOTH match, I want the total of Column B figures for the match ONLY. Otherwise the result should be zero. The result I keep receiveing is DOUBLE the amount of the ENTIRE Column B not just the matched criteria. HELP!! -- Thank you for your help MO Albany, NY |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SUMPRODUCT
I get VALUE. When I take the "," out, I a total of ALLof Column B rather
than just the total of the information that has "NETSMART" and 53000.5300700001.0030.228200.20070.56995.FMS Any suggestions? Thank you for your help MO Albany, NY "Bob Phillips" wrote: There is a missing comma =SUMPRODUCT(--ISNUMBER(SEARCH("NETSMART",'07-08 Pd. in 07-08-22820'!$A$4:$A$1000('07-08 Pd. in 07-08-22820'!$E$4:$E$1000="53000.5300700001.0030.228200. 20070.56995.FMS"))),--('07-08 Pd. in 07-08-22820'!$B$4:$B$1000)) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "MO" wrote in message ... I need to create a formula where 2 column criteria must match--Columns A&E (See Below) Column A may only have partial information that matches (i.e. it may show "Netsmart USA" one time and just "Netsmart"the next time--but it will always show at least "Netsmart"). Column E will always be the same (i.e., "53000.5300700001.00300.228200.20070.56995.FMS ") __________________________________________________ ______________ FORMULA =SUMPRODUCT(--ISNUMBER(SEARCH("NETSMART",'07-08 Pd. in 07-08-22820'!$A$4:$A$1000('07-08 Pd. in 07-08-22820'!$E$4:$E$1000="53000.5300700001.0030.228200. 20070.56995.FMS")))--('07-08 Pd. in 07-08-22820'!$B$4:$B$1000)) __________________________________________________ _______________ If Columns A&E BOTH match, I want the total of Column B figures for the match ONLY. Otherwise the result should be zero. The result I keep receiveing is DOUBLE the amount of the ENTIRE Column B not just the matched criteria. HELP!! -- Thank you for your help MO Albany, NY |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SUMPRODUCT
Does the order of columns matter in SUMPRODUCT as it does in LOOKUP
functions? ie: If I had data in Column J to sum, but the two criterion were in columns K and M, would if affect the outcome at all? |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SUMPRODUCT
Nope.
=sumproduct(--(sheet2!a1:a10=a1),--(sheet2!c1:c10=c1),sheet2!d1:d10) should return the same as: =sumproduct(--(sheet2!c1:c10=c1),--(sheet2!a1:a10=a1),sheet2!d1:d10) and =sumproduct(sheet2!d1:d10,--(sheet2!c1:c10=c1),--(sheet2!a1:a10=a1)) (well, if I fixed each formula ok <vbg.) Kigol wrote: Does the order of columns matter in SUMPRODUCT as it does in LOOKUP functions? ie: If I had data in Column J to sum, but the two criterion were in columns K and M, would if affect the outcome at all? -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SUMPRODUCT
I don't know. Do you have an alternative for a formula? I need to look at
Columns A&E and if both meet the criteria, I need to add Column B (ONLY the criteria that meets). -- Thank you for your help MO Albany, NY "Kigol" wrote: Does the order of columns matter in SUMPRODUCT as it does in LOOKUP functions? ie: If I had data in Column J to sum, but the two criterion were in columns K and M, would if affect the outcome at all? |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SUMPRODUCT
Does this formula do what you want?
=SUMPRODUCT(--ISNUMBER((SEARCH("NETSMART",'07-08 Pd. in 07-08-22820'!$A$4:$A$1000))),--('07-08 Pd. in 07-08-22820'!$E$4:$E$1000="53000.5300700001.00300.228200 .20070.56995.FMS"),'07-08 Pd. in 07-08-22820'!$B$4:$B$1000) Rick "MO" wrote in message ... I need to create a formula where 2 column criteria must match--Columns A&E (See Below) Column A may only have partial information that matches (i.e. it may show "Netsmart USA" one time and just "Netsmart"the next time--but it will always show at least "Netsmart"). Column E will always be the same (i.e., "53000.5300700001.00300.228200.20070.56995.FMS ") __________________________________________________ ______________ FORMULA =SUMPRODUCT(--ISNUMBER(SEARCH("NETSMART",'07-08 Pd. in 07-08-22820'!$A$4:$A$1000('07-08 Pd. in 07-08-22820'!$E$4:$E$1000="53000.5300700001.0030.228200. 20070.56995.FMS")))--('07-08 Pd. in 07-08-22820'!$B$4:$B$1000)) __________________________________________________ _______________ If Columns A&E BOTH match, I want the total of Column B figures for the match ONLY. Otherwise the result should be zero. The result I keep receiveing is DOUBLE the amount of the ENTIRE Column B not just the matched criteria. HELP!! -- Thank you for your help MO Albany, NY |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SUMPRODUCT
THANK YOU THANK YOU THANK YOU. You have no clue how helpful this is :)
-- MO Albany, NY "Rick Rothstein (MVP - VB)" wrote: Does this formula do what you want? =SUMPRODUCT(--ISNUMBER((SEARCH("NETSMART",'07-08 Pd. in 07-08-22820'!$A$4:$A$1000))),--('07-08 Pd. in 07-08-22820'!$E$4:$E$1000="53000.5300700001.00300.228200 .20070.56995.FMS"),'07-08 Pd. in 07-08-22820'!$B$4:$B$1000) Rick "MO" wrote in message ... I need to create a formula where 2 column criteria must match--Columns A&E (See Below) Column A may only have partial information that matches (i.e. it may show "Netsmart USA" one time and just "Netsmart"the next time--but it will always show at least "Netsmart"). Column E will always be the same (i.e., "53000.5300700001.00300.228200.20070.56995.FMS ") __________________________________________________ ______________ FORMULA =SUMPRODUCT(--ISNUMBER(SEARCH("NETSMART",'07-08 Pd. in 07-08-22820'!$A$4:$A$1000('07-08 Pd. in 07-08-22820'!$E$4:$E$1000="53000.5300700001.0030.228200. 20070.56995.FMS")))--('07-08 Pd. in 07-08-22820'!$B$4:$B$1000)) __________________________________________________ _______________ If Columns A&E BOTH match, I want the total of Column B figures for the match ONLY. Otherwise the result should be zero. The result I keep receiveing is DOUBLE the amount of the ENTIRE Column B not just the matched criteria. HELP!! -- Thank you for your help MO Albany, NY |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sumproduct with Condition OR Sumproduct with ADDRESS function - HE | Excel Discussion (Misc queries) | |||
| SUMPRODUCT | Excel Worksheet Functions | |||
| sumproduct - < | Excel Discussion (Misc queries) | |||
| Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
| sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
Linear Mode
