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

 

[AnotherHiggins]

What version of Excel are you using? In 2007 and later you could just use SumIfs.

[COLOR=blue white]=SUMIFS(M:M,B:B, "11_22", L:L, 303, M:M, ">=" & 1)[/color]
(Not entered as an array formula)

That'll be much simpler going forward.

To add your second criteria, just add two formulas together to make a longer formula:

[COLOR=blue white]=SUMIFS(M:M,B:B, "11_22", L:L, 303, M:M, ">=" & 1)
+SUMIFS(M:M,B:B, "11_22", L:L, 1802, M:M, ">=" & 1)[/color]


As for why your second formula failed, keep in mind that an IF formula works like this: If(Criteria, ResultIfTrue, ResultIfFalse) You're only looking for 1802 in column L if column L contains 303. That's impossible.

Instead you need to put the "$L$1:$L$500=1802" test in the ResultIfFalse section of it's parent IF statement. Like this:

[COLOR=blue white]=SUM(IF($B$1:$B$500="11_22", IF($L$1:$L$500=303, IF($M$1:$M$500>=1, $M$1:$M$500), if($L$1:$L$500=1802, IF($M$1:$M$500>=1, $M$1:$M$500)))))[/color]

But if you have 2003 or earlier, I prefer using SumProduct because it's very easy to follow the logic and add new criteria:

[COLOR=blue white]=SUMPRODUCT( ($B$1:$B$500="11_22") * ($L$1:$L$500=303) * ($M$1:$M$500>=1) * ($M$1:$M$500) )[/color]

Again, you'll add two formulas together for the second criteria

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[jrobin9151]

Higgins Thank You - I have 2003 edition of office and when I enter the sumproduct formula I get a #Value error. Why is this occuring?

 

[SkipVought]

Please post the exact formula are using.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jrobin9151]

Higgins, you got me to thinking and i came up with the following formula, although long and ugly it works!!
=SUM(IF($B$1:$B$500="11_22",IF($L$1:$L$500=303,IF($M$1:$M$500>=1,$M$1:$M$500))))+SUM(IF($B$1:$B$500="11_22",IF($L$1:$L$500=856,IF($M$1:$M$500>=1,$M$1:$M$500))))+SUM(IF($B$1:$B$500="11_22",IF($L$1:$L$500=1515,IF($M$1:$M$500>=1,$M$1:$M$500))))+SUM(IF($B$1:$B$500="11_22",IF($L$1:$L$500=1802,IF($M$1:$M$500>=1,$M$1:$M$500))))

 

[macropod]

Hi jrobin,

Try:
=SUM(IF(($B$1:$B$500="11_22")*(($L$1:$L$500=303)+($L$1:$L$500=856)+($L$1:$L$500=1802)+($L$1:$L$500=1515))*($M$1:$M$500>=1),$M$1:$M$500))

Cheers
Paul Edstein
[MS MVP - Word]

 

[macropod]

Alternatively, as an ordinary formula:
=SUMPRODUCT(($B$1:$B$500="11_22")*(($L$1:$L$500=303)+($L$1:$L$500=856)+($L$1:$L$500=1802)+($L$1:$L$500=1515))*($M$1:$M$500>=1),$M$1:$M$500)

Cheers
Paul Edstein
[MS MVP - Word]

 

[jrobin9151]

Happy Holidays all and thanks so much for your help. Macropod, your suggestion greatly reduced my formula and made it much more sensisble. I now have a last formula that I'm trying to constuct that will sum the 1's in column M that correspond to the selected pay period but are NOT a series that is listed in the formula. I've tried the NOT EQUAL to symbol <> but it is adding all the ones in the column regardless of the payperiod or series. I don't understand why. Here is the formula
=SUM(IF(($B$1:$B$500="11_22")*(($L$1:$L$500<>180)+($L$1:$L$500<>856)+($L$1:$L$500<>1802)+($L$1:$L$500<>1515))*($M$1:$M$500>=1),$M$1:$M$500))

 

[macropod]

Hi jrobin,

So why not:
=SUM($M$1:$M$500)-SUM(IF(($B$1:$B$500="11_22")*(($L$1:$L$500=303)+($L$1:$L$500=856)+($L$1:$L$500=1802)+($L$1:$L$500=1515))*($M$1:$M$500>=1),$M$1:$M$500))
or:
=SUM($M$1:$M$500)-SUMPRODUCT(($B$1:$B$500="11_22")*(($L$1:$L$500=303)+($L$1:$L$500=856)+($L$1:$L$500=1802)+($L$1:$L$500=1515))*($M$1:$M$500>=1),$M$1:$M$500)

Cheers
Paul Edstein
[MS MVP - Word]

 

[jrobin9151]

Happy New Year All!!!

The formula is not working. I'm trying to determine then number of staff not defined with a certain code in column L during a pay-period determined in colum B.

As suggested by Macropod I put this formula in T30 as an array {=SUM($M$1:$M$50)-SUM(IF(($B$1:$B$50="11_22")*(($L$1:$L$50=303)+($L$1:$L$50=856)+($L$1:$L$50=1802)+($L$1:$L$50=1515))*($M$1:$M$50>=1),$M$1:$M$50))}

It returns the number 4, I want to see a 1 as the result because in pay period 11_22 the formula should only count M2 and disregard M3 and M4.

B2 = 11_22 L2 = 200 M2 = 1
B3 = 11_22 L3 = 1515 M3 = 1
B4 = 11_22 L4 = 303 M4 = 1
B5 = 11_25 L5 = 180 M5 = 1
B6 = 11_25 L6 = 303 M6 = 1
B7 = 11_23 L7 = 303 M7 =
B8 = 11_23 L8 = 856 M8 = 1
B9 = 11_23 L9 = 1515 M9 =

 

[SkipVought]

[tt]
=SUMPRODUCT((B2:B9="11_22")*(L2:L9=303)*(M2:M9>=1))
+SUMPRODUCT((B2:B9="11_22")*(L2:L9=856)*(M2:M9>=1))
+SUMPRODUCT((B2:B9="11_22")*(L2:L9=1802)*(M2:M9>=1))
+SUMPRODUCT((B2:B9="11_22")*(L2:L9=1515)*(M2:M9>=1))
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jrobin9151]

Skip,

Thanks but now the formula returns the number two. Using the dataset above I should end up with a 1. Because, cell B2 - "11_22" cell L2 is 200 and M2 = 1. I want the formula to disregard summing M3 and M4 because Cell L3 = 1515 and Cell L4 = 303 even tho all three are in the same pay-period.

Formula I'm using, cut and pasted here
=SUMPRODUCT((B2:B9="11_22")*(L2:L9=303)*(M2:M9>=1))
+SUMPRODUCT((B2:B9="11_22")*(L2:L9=856)*(M2:M9>=1))
+SUMPRODUCT((B2:B9="11_22")*(L2:L9=1802)*(M2:M9>=1))
+SUMPRODUCT((B2:B9="11_22")*(L2:L9=1515)*(M2:M9>=1))

 

[SkipVought]

Please state you logic clearly, concisely and completely.

Please include the criteria that must be used.

What you have already stated is not clear.



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jrobin9151]

The spreadsheet is set up as such:
Each row is a record
Col A = Month
Col B = Pay Period
Col L = Job Type
Col M = The number 1 is placed in the cell if its a gain, if it is a loss then a 1 is placed in column N

I am attempting to add all of the gains OR all of the losses for a specfic pay period and a specific job.

As an example: In pay period 11_22 I have gains and I want to sum the number 1's in column M where the Job type is NOT equal to 1515 or 303.
- I do not know what the end user will put as a job type in some instances so I'm trying to sum those against the known job types.
- So for my example there is one 1515 job type and one 303 job type and one 200 job type.

I've tried summing all of the 1's in column M and subtracting the 'known' job types but then It ends up counting in other pay periods. I want to calculate the number of gains, for a particular series, for each pay-period exclusively.

 

[SkipVought]

Under consideration are THREE columns of data.

In this logic, does it really matter what specific values are in column L? Are the relevant criteria 1) a specific value in column B and at least one value >=1 in column M?

If that is the case, then the logic would be stated...
[tt]
if column B equals 11_22 and the sum in column M greater than zero, then return 1 else return 0.
[/tt]
And if that is the case then...
[tt]
=IF(SUMIF(B2:B9,"11_22",M2:M9)>0,1,0)
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Sorry, I posted before I saw your last post.

I want to sum the number 1's in column M where the Job type is NOT equal to 1515 or 303.
- [highlight]I do not know what the end user will put as a job type[/highlight] in some instances so I'm trying to sum those against the known job types.

[highlight]Your requirement is a PROBLEM![/highlight] Do you not have a fixed list of Job Types? Do you not know which Job Types to include or not?

SO...

How about looking at the formula I posted and use your imagination about the OPERATORS separating the SUMPRODUCT functions.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jrobin9151]

Skip, thanks for hanging in there with me on this.

- Yes, Under consideration are three columns of data

In this logic, does it really matter what specific values are in column L? Are the relevant criteria 1) a specific value in column B and at least one value >=1 in column M? The answer is yes. I want to sum col m. where the values in column are NOT job types specified in the formula.

For example:
If column B equals 11_22 AND Column L (Not Equal <>) to the values I list in the compound formula, THEN add the value in M to the other job series in Col M that are in the same pay period(col B value)

 

[SkipVought]

As I stated in my previous post, look at my previously posted formula and modify as needed...
[tt]
=SUMPRODUCT((B2:B9="11_22")*(L2:L9=303)*(M2:M9>=1))
+SUMPRODUCT((B2:B9="11_22")*(L2:L9=856)*(M2:M9>=1))
+SUMPRODUCT((B2:B9="11_22")*(L2:L9=1802)*(M2:M9>=1))
+SUMPRODUCT((B2:B9="11_22")*(L2:L9=1515)*(M2:M9>=1))
[/tt]
And what about the TWO questions I raised in my previous post???


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jrobin9151]

Skip,

Okay a foulup had occured in copy and pasting from your first post. My apologies....

The job types are from a selected list that I have in a combo box but the user can also enter other types. Hence, the variables.. so you are dead on, the requirments are a big problem. The end user wants to know the amount of each gains per specific type of job and then has a catchall. So count the job if it fits this criteria and break it out and then we want you to lump all of the other jobs that are not listed as one number.

Thank you all for your assistance. The formula you helped me with works. I'll continue to try and refine it as the end user requirements become clearer.

 

[SkipVought]

Consider using another 'helper' column to use as a grouping column. Use you list and the MATCH function to determine what your groupings should be. When you get an N/A, return the 'OTHER' or whatever you want to call the catch-all.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]