| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUM IF AND
Please see formula below:
I'm trying to have the formula add currency (column B) but it must meet two criteria (column A[may not always have the whole name] & column E). Everything I've tried doesn't work. Can you assist? =SUMIF(AND('07-08 Pd. in 07-08-22820'!"53000.5300700001.00300.228200.20070.56995. FMS",'07-08 Pd. in 07-08-22820'!"NETSMART NEW YORK INC")(A4:A1000)(E4:E1000),'07-08 Pd. in 07-08-22820'!B4:B1000,'07-08 Pd. in 07-08-22820'!) -- Thank you for your help MO Albany, NY |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SUM IF AND
I couldn't make much out of your example, but
Possibly something like this: =sumproduct(--isnumber(Search("some value",$A$4:$A$1000)),--($E$4:$E$1000="ABC"),$B$4:$B$1000) Use the same set up I used for A for E if you are looking for a substring there as well. -- Regards, Tom Ogilvy "MO" wrote: Please see formula below: I'm trying to have the formula add currency (column B) but it must meet two criteria (column A[may not always have the whole name] & column E). Everything I've tried doesn't work. Can you assist? =SUMIF(AND('07-08 Pd. in 07-08-22820'!"53000.5300700001.00300.228200.20070.56995. FMS",'07-08 Pd. in 07-08-22820'!"NETSMART NEW YORK INC")(A4:A1000)(E4:E1000),'07-08 Pd. in 07-08-22820'!B4:B1000,'07-08 Pd. in 07-08-22820'!) -- Thank you for your help MO Albany, NY |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SUM IF AND
Excellent and thank you.
-- Thank you for your help MO Albany, NY "Tom Ogilvy" wrote: I couldn't make much out of your example, but Possibly something like this: =sumproduct(--isnumber(Search("some value",$A$4:$A$1000)),--($E$4:$E$1000="ABC"),$B$4:$B$1000) Use the same set up I used for A for E if you are looking for a substring there as well. -- Regards, Tom Ogilvy "MO" wrote: Please see formula below: I'm trying to have the formula add currency (column B) but it must meet two criteria (column A[may not always have the whole name] & column E). Everything I've tried doesn't work. Can you assist? =SUMIF(AND('07-08 Pd. in 07-08-22820'!"53000.5300700001.00300.228200.20070.56995. FMS",'07-08 Pd. in 07-08-22820'!"NETSMART NEW YORK INC")(A4:A1000)(E4:E1000),'07-08 Pd. in 07-08-22820'!B4:B1000,'07-08 Pd. in 07-08-22820'!) -- Thank you for your help MO Albany, NY |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SUM IF AND
Sorry, I thought it was going to work.
2 colums criteria must match--Columns A&E. Column A may only have partial inforamtion that matches, therefore (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 " If they BOTH match, I want to total Column B. Otherwise the result should be zero. -- Thank you for your help MO Albany, NY "Tom Ogilvy" wrote: I couldn't make much out of your example, but Possibly something like this: =sumproduct(--isnumber(Search("some value",$A$4:$A$1000)),--($E$4:$E$1000="ABC"),$B$4:$B$1000) Use the same set up I used for A for E if you are looking for a substring there as well. -- Regards, Tom Ogilvy "MO" wrote: Please see formula below: I'm trying to have the formula add currency (column B) but it must meet two criteria (column A[may not always have the whole name] & column E). Everything I've tried doesn't work. Can you assist? =SUMIF(AND('07-08 Pd. in 07-08-22820'!"53000.5300700001.00300.228200.20070.56995. FMS",'07-08 Pd. in 07-08-22820'!"NETSMART NEW YORK INC")(A4:A1000)(E4:E1000),'07-08 Pd. in 07-08-22820'!B4:B1000,'07-08 Pd. in 07-08-22820'!) -- Thank you for your help MO Albany, NY |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SUM IF AND
Suggested a solution in response to your later post.
-- Regards, Tom Ogilvy "MO" wrote: Sorry, I thought it was going to work. 2 colums criteria must match--Columns A&E. Column A may only have partial inforamtion that matches, therefore (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 " If they BOTH match, I want to total Column B. Otherwise the result should be zero. -- Thank you for your help MO Albany, NY "Tom Ogilvy" wrote: I couldn't make much out of your example, but Possibly something like this: =sumproduct(--isnumber(Search("some value",$A$4:$A$1000)),--($E$4:$E$1000="ABC"),$B$4:$B$1000) Use the same set up I used for A for E if you are looking for a substring there as well. -- Regards, Tom Ogilvy "MO" wrote: Please see formula below: I'm trying to have the formula add currency (column B) but it must meet two criteria (column A[may not always have the whole name] & column E). Everything I've tried doesn't work. Can you assist? =SUMIF(AND('07-08 Pd. in 07-08-22820'!"53000.5300700001.00300.228200.20070.56995. FMS",'07-08 Pd. in 07-08-22820'!"NETSMART NEW YORK INC")(A4:A1000)(E4:E1000),'07-08 Pd. in 07-08-22820'!B4:B1000,'07-08 Pd. in 07-08-22820'!) -- Thank you for your help MO Albany, NY |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Hybrid Mode
