| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct for partial name?
I have a chart of accounts. All accounts have a hyphen separating the
main account and sub account. I have a sumproduct formula that returns the sum of all main & sub accounts such as Advertising- Billboards. But I need a sumproduct formula to return the total of all Advertising too. A formula that looks at the main account BEFORE the hyphen. Is that possible? Advertising-Radio Advertising-ROP Advertising-Direct Mail Advertising-Billboards Advertising-TV Advertising-Broadcast |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumproduct for partial name?
Hi
This should do it: =SUMPRODUCT(--(LEFT(A1:A100,11)="Advertising"),B1:B100) Regards, Per On 29 Dec., 15:28, wx4usa wrote: I have a chart of accounts. All accounts have a hyphen separating the main account and sub account. *I have a sumproduct formula that returns the sum of all main & sub accounts such as Advertising- Billboards. *But I need a sumproduct formula to return the total of all Advertising too. A formula that looks at the main account BEFORE the hyphen. Is that possible? Advertising-Radio Advertising-ROP Advertising-Direct Mail Advertising-Billboards Advertising-TV Advertising-Broadcast |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumproduct for partial name?
Hi, You should be able to use the 'wildcard characters' specifficly the * one
sounds like the one that you'll need in your formula -- Jim "wx4usa" wrote: I have a chart of accounts. All accounts have a hyphen separating the main account and sub account. I have a sumproduct formula that returns the sum of all main & sub accounts such as Advertising- Billboards. But I need a sumproduct formula to return the total of all Advertising too. A formula that looks at the main account BEFORE the hyphen. Is that possible? Advertising-Radio Advertising-ROP Advertising-Direct Mail Advertising-Billboards Advertising-TV Advertising-Broadcast . |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumproduct for partial name?
Not in =sumproduct() formulas.
But if that formula isn't checking multiple criteria, then maybe =sumif() would work: =sumif(a:a,"advertising*",b:b) In xl2007, =sumifs() may even do everything the user wants. But without knowing the original formula, that's just a guess. Jim wrote: Hi, You should be able to use the 'wildcard characters' specifficly the * one sounds like the one that you'll need in your formula -- Jim "wx4usa" wrote: I have a chart of accounts. All accounts have a hyphen separating the main account and sub account. I have a sumproduct formula that returns the sum of all main & sub accounts such as Advertising- Billboards. But I need a sumproduct formula to return the total of all Advertising too. A formula that looks at the main account BEFORE the hyphen. Is that possible? Advertising-Radio Advertising-ROP Advertising-Direct Mail Advertising-Billboards Advertising-TV Advertising-Broadcast . -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| sumproduct with partial charcter matching | Excel Worksheet Functions | |||
| Find partial match from column A,B and fill partial match in C? | Excel Discussion (Misc queries) | |||
| Sumproduct for partial text | Excel Worksheet Functions | |||
| sumproduct partial text count | Excel Worksheet Functions | |||
| Partial replace | New Users to Excel | |||
Linear Mode
