| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find a word in a table, then use a function
Hi everybody
I have a table with the days of the week, and the shifts for each one eg S1 Sun S2 Sun S3 Sun S1 Mon S2 Mon etc on column A and in column B, the number of items manufactured On another table, (column D) i have ONLY the days Sun Mon Tue etc What I want to do is to sum in column E the total for each day, by reference to column D something like "find this value in the other table, and add them" usually i would have use the sumif my problem is with the shifts so i need to find the cells that contains especific text to sum |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find a word in a table, then use a function
Try this in E1:
=SUMPRODUCT((A$1:A$21<"")*(RIGHT(A$1:A$21,3)=D1)* (B$1:B$21)) then copy into E2:E7. Hope this helps. Pete On Oct 2, 5:14*pm, Alonso wrote: Hi everybody I have a table with the days of the week, and the shifts for each one eg S1 Sun S2 Sun S3 Sun S1 Mon S2 Mon etc on column A and in column B, the number of items manufactured On another table, (column D) i have ONLY the days Sun Mon Tue etc What I want to do is to sum in column E the total for each day, by reference to column D something like "find this value in the other table, and add them" usually i would have use the sumif my problem is with the shifts so i need to find the cells that contains especific text to sum |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find a word in a table, then use a function
Thanks Pete
it worked just great "Pete_UK" wrote: Try this in E1: =SUMPRODUCT((A$1:A$21<"")*(RIGHT(A$1:A$21,3)=D1)* (B$1:B$21)) then copy into E2:E7. Hope this helps. Pete On Oct 2, 5:14 pm, Alonso wrote: Hi everybody I have a table with the days of the week, and the shifts for each one eg S1 Sun S2 Sun S3 Sun S1 Mon S2 Mon etc on column A and in column B, the number of items manufactured On another table, (column D) i have ONLY the days Sun Mon Tue etc What I want to do is to sum in column E the total for each day, by reference to column D something like "find this value in the other table, and add them" usually i would have use the sumif my problem is with the shifts so i need to find the cells that contains especific text to sum |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find a word in a table, then use a function
Glad to hear it - thanks for feeding back.
Pete On Oct 2, 5:58*pm, Alonso wrote: Thanks Pete it worked just great |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find function Pivot Table | Excel Discussion (Misc queries) | |||
| Link table from excel to word using word VBA | Excel Discussion (Misc queries) | |||
| What function will find a value in a table with X & Y Values | Excel Worksheet Functions | |||
| Can I Find one word in a formula | Excel Worksheet Functions | |||
| Find a word and then take certain action, else | Excel Worksheet Functions | |||
![Alonso[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
