Multiple IFs within an Array 1

[jrobin9151]

I put together the following formula based on blogs out there and it works fine: {=SUM(IF($B$1:$B$500="11_22",IF($L$1:$L$500=303,IF($M$1:$M$500>=1,$M$1:$M$500))))}

The formula looks to column B and when it finds the Pay period "11_22" it looks to column L for cells that have series 303 listed. After those matches return true it looks to column M for entries that are greater than or equal to 1 and sums it returns the sum. Again, this formula works fine.

ISSUE:
The breakdown occurs when I add a second series such as 1802. So I want the series to add the 1's in column M when it finds series 303 OR 1802 in column L. I tried this formula {=SUM(IF($B$1:$B$500="11_22",IF($L$1:$L$500=303,IF($L$1:$L$500=1802,IF($M$1:$M$500>=1,$M$1:$M$500))))} and I don't get an error but it does not add the values corresponding to the pay period or series. I tried sticking an OR(if,.... to find the second series value but that didn't work either.

END PURPOSE:
I know I could get away with using a pivot table for the data but I'm dealing with novices so I"m trying to parse out this information from a data dump and have it post to a "recap" page

 

[jrobin9151]

Skip,

I re-arranged my spreadsheet and dramatically cut down on formula length.

I threw in a helper column and added this formula:
=IF(ISERROR(MATCH(L2,$Y$38:$Y$47,0)),1,0)
Which looks to see if the job listed matches my master list. If not it puts a 1 in my helper column

Then I added below where I recap to determine how many "Other" type jobs were added during the specific pay period
=SUMPRODUCT((B1:B13="11_22")*(T1:T13>=1))
Which ADDS all of the cells >= 1 in the T column where the pay period is 11_22!!!

Works superb!! Thanks so much

 

[kjv1611]

jrobin9151,

Please mark whichever post(s) which were most helpful by clicking on Thank so and so and star this post!. That way when someone else has the same question, they can come here, and hopefully find the answer they need for their particular problem. If more than one person was helpful, pick the most helpful post for each person. You can only mark one post helpful per member.

Using this star system isn't fool-proof, but it does help to quickly find the best solution to questions/problems on occasion.


"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57