Sum cells based on text

[Greed]

I am creating a spreadsheet for payroll that would be huge without the use of text and numerical values together. At its most complicated, a cell could look like this:

APCSTAT 10 LB4 (Acting PC on a STAT for 10 hours submitted late by 4 weeks)

I have come up with this formula which works for summing the hours worked:

=SUMPRODUCT(--(TRIM(REPLACE($C$5:$I$5,1,SEARCH(" ",$C$5:$I$5&" "),""))="AC"),--LEFT(0&$C$5:$I$5,SEARCH(" ",$C$5:$I$5&" ")))*5.39

but I still need a formula to catch late entries (cells containing LB#) since the above formula does not capture them at all...

Since this is a new process- I am open to ideas to better track this information, bearing in mind there are aprox. 200 employees and this is a 24/7 operation, so there will be a lot of information
dummy workbook attached - thanks!

[NBVC]

Can you give an example of how the LB is to be incorporated... i.e. expected results?

[Greed]

We need to be able to enter any late submissions of overtime hours in the correct cell for that particular date, even if the hours were submitted too late to pay out during that pay period. I need to use the code LB# to collect the hours in another colum to be added to the current pay period...what ever period that might be. Problem is, the formula I am currently using does not collect anything from the cells showing that code LB#) so I guess I need an altered formula to collect both numbers in the code (# OT LB #) and a new formula to collect the just # in the LB# code. Thanks

[NBVC]

So you are saying that the value in M6 should be (8+8+8)*1.5 or 36? Is that correct?

I.e. you want to count all the OTOT values regardless if there is an LB code or not?

[Greed]

Yes, exactly - and then a different formula to then capture only the # of hours included in a code with LB (ie. total only the cells with LB#) - thanks

[NBVC]

See attached for a suggested solution.

I inserted a row between rows 1 and 2 and inserted the actual search codes in there. This new row can be hidden.

I also changed the multiplication factors to actual numerics.

These changes are made so that we can create one formula that can be copied down and across the matrix and have the conditions automatically adjusted as you copy down and over.

So in the new J5 cell enter formula:

Please Login or Register  to view this content.

This formula needs to be confirmed with CTRL+SHIFT+ENTER not just ENTER and copied down and across to column T.

this should give the desired results.

Now in V2 enter LB and in in V3 the multiplication factor (1 or whatever you choose).

Then in V5 enter:

Please Login or Register  to view this content.

also confirmed with CTRL+SHIFT+ENTER and copied down.

[Greed]

Fab! You Rock - thanks so much. I can only hope to be so proficient one day