Array formula to pull multiple numbers based on test

Hi all,

I have a payroll tracking workbook that has been very useful until now. The workbook consists of entry sheets (for entering # of hours and OT codes) and reporting sheets (to pull the # of hours from the entry sheet and instert into the correct column).
So far, so good. Problem is, I have run up against an unforseen issue - occassionally, an employee may require the entry of 2 different codes on the same day. The current formula cannot make that allowance and therefore misses 1 of the codes 9actually gets confused and pulls the wrong numer for 1 code and ignores the other completely). Regretfully, this is beyond my abilities.

Here is the current formula:

=SUM(IF(ISNUMBER(FIND(" "&Q$3&" "," "&'sheet1'!$AS61:$BF61&" ")),LEFT('sheet1'!$AS61:$BF61,FIND(" ",'sheet1'!$AS61:$BF61)-1)+0))+SUM(IF(ISNUMBER(FIND(" "&Q$3&" "&"P23"&" "," "&'sheet1'!$AE61:$AR61&" ")),LEFT('sheet1'!$AE61:$AR61,FIND(" ",'sheet1'!$AE61:$AR61)-1)+0))

*The second part of the formula (after the '+' - in blue) is just a copy of the first, giving the command to look back for the code for 'late entries' (P#)

Workbook attached - any help would be greatly appreciated! Getting desperate here

Try, in G10:

=SUM(IF(ISNUMBER(FIND(" "&G3&" "," "&SUBSTITUTE('Q4 - Nov 1 to Dec 31 2011'!$C13:$P13,CHAR(10)," ")&" ")),MID(" "&SUBSTITUTE('Q4 - Nov 1 to Dec 31 2011'!$C13:$P13,CHAR(10)," ")&" ",FIND(" "&G3&" "," "&SUBSTITUTE('Q4 - Nov 1 to Dec 31 2011'!$C13:$P13,CHAR(10)," ")&" ")-2,2)+0))

Confirmed with CTRL+SHIFT+ENTER. copied to other directions.

This formula assumes that the numbers for each code entered will always be 1 or 2 digits only..

From PM:

Hi - sorry to bother you, but I have just discovered that the formula isn't catchin numbers with a decimal - anything you can tweak in here for that? TIA

=SUM(IF(ISNUMBER(FIND(" "&C$3&" "," "&SUBSTITUTE('Q4 - Nov 1 to Dec 31 2011'!$Q7:$AD7,CHAR(10)," ")&" ")),MID(" "&SUBSTITUTE('Q4 - Nov 1 to Dec 31 2011'!$Q7:$AD7,CHAR(10)," ")&" ",FIND(" "&C$3&" "," "&SUBSTITUTE('Q4 - Nov 1 to Dec 31 2011'!$Q7:$AD7,CHAR(10)," ")&" ")-2,2)+0))

How many decimals? Can you give another spreadsheet sampling?

Does this in D10 work?

=SUM(IF(ISNUMBER(FIND(" "&D$3&" ","      "&SUBSTITUTE('Q4 - Nov 1 to Dec 31 2011'!$C13:$P13,CHAR(10),"      ")&"      ")),MID("      "&SUBSTITUTE('Q4 - Nov 1 to Dec 31 2011'!$C13:$P13,CHAR(10),"      ")&"      ",FIND(" "&D$3&" ","      "&SUBSTITUTE('Q4 - Nov 1 to Dec 31 2011'!$C13:$P13,CHAR(10),"      ")&"      ")-5,5)+0))

copied to other cells..

I am not understanding the P21 add on. So it's supposed to count the number of P21 that appear in the whole row and add to the original formula?

btw, this formula in G4:

=SUM(IF(ISNUMBER(FIND(" "&G$3&" ","      "&SUBSTITUTE(SUBSTITUTE('Q4 - Nov 1 to Dec 31 2011'!$Q7:$AD7," ","      "),CHAR(10),"      ")&"      ")),MID("      "&SUBSTITUTE(SUBSTITUTE('Q4 - Nov 1 to Dec 31 2011'!$Q7:$AD7," ","      "),CHAR(10),"      ")&"      ",FIND(" "&G$3&" ","      "&SUBSTITUTE(SUBSTITUTE('Q4 - Nov 1 to Dec 31 2011'!$Q7:$AD7," ","      "),CHAR(10),"      ")&"      ")-10,10)+0))

CSE confirmed... should now allow for codes separated with a space or with ALT+ENTER carriage return.

If you did want to count and add to the above formula, then:

=SUM(IF(ISNUMBER(FIND(" "&G$3&" ","      "&SUBSTITUTE(SUBSTITUTE('Q4 - Nov 1 to Dec 31 2011'!$Q7:$AD7," ","      "),CHAR(10),"      ")&"      ")),MID("      "&SUBSTITUTE(SUBSTITUTE('Q4 - Nov 1 to Dec 31 2011'!$Q7:$AD7," ","      "),CHAR(10),"      ")&"      ",FIND(" "&G$3&" ","      "&SUBSTITUTE(SUBSTITUTE('Q4 - Nov 1 to Dec 31 2011'!$Q7:$AD7," ","      "),CHAR(10),"      ")&"      ")-10,10)+0))+COUNTIF('Q4 - Nov 1 to Dec 31 2011'!$C7:$DJ7,"*P21*")

You can change the P21 to "*"&$A$1&"*" where A1 has current payperiod (P21) in it... so that you can have a more dynamic formula.

Ok, I think I understand... I think

Try, in C4:

=SUM(IF(ISNUMBER(FIND(" "&C$3&" ","      "&SUBSTITUTE(SUBSTITUTE('Q4 - Nov 1 to Dec 31 2011'!$Q7:$AD7," ","      "),CHAR(10),"      ")&"      ")),MID("      "&SUBSTITUTE(SUBSTITUTE('Q4 - Nov 1 to Dec 31 2011'!$Q7:$AD7," ","      "),CHAR(10),"      ")&"      ",FIND(" "&C$3&" ","      "&SUBSTITUTE(SUBSTITUTE('Q4 - Nov 1 to Dec 31 2011'!$Q7:$AD7," ","      "),CHAR(10),"      ")&"      ")-10,10)+0))+SUM(IF(ISNUMBER(SEARCH("P21",'Q4 - Nov 1 to Dec 31 2011'!$C7:$DJ7)),IF(ISNUMBER(FIND(" "&C$3&" ","      "&SUBSTITUTE(SUBSTITUTE('Q4 - Nov 1 to Dec 31 2011'!$C7:$DJ7," ","      "),CHAR(10),"      ")&"      ")),MID("      "&SUBSTITUTE(SUBSTITUTE('Q4 - Nov 1 to Dec 31 2011'!$C7:$DJ7," ","      "),CHAR(10),"      ")&"      ",FIND(" "&C$3&" ","      "&SUBSTITUTE(SUBSTITUTE('Q4 - Nov 1 to Dec 31 2011'!$C7:$DJ7," ","      "),CHAR(10),"      ")&"      ")-10,10)+0)))

CSE confirmed and copied down and across.

No formal schooling.. just picked everything up being on these forums

Teylyn is our "Excel chick" here