sum if multiple conditions are met, where order of numbers in array matters

**kileyc** · 11-19-2012, 01:05 AM

Hi! I am trying to come up with an excel formula for the following situation:

I have a table with 1000 rows down and 10 columns across. In the array for each row there could be a number in 1 to 10 of the columns. For each row, if there is a number in column K there must also be a number in columns B,C,D,E,F,G,H,I and J. Similarly if there is a number in column E there must also be a number in B,C and D.

I need to make a formula that does the following:

First, it looks to see if any number in B:K of each row is above X. If a number is above X then I need to find out if there is a second number in the row above Y. If there is a number above Y then I need to return the min of that number minus X and another number Z.

Please let me know if you can help!

THANK YOU!
KC

**Vinoth Kanna** · 11-19-2012, 01:25 AM

It will be great if you provide a sample data sheet for your problem. Thanks.

Regards,
Vikan

**dilipandey** · 11-19-2012, 02:38 AM

Hi Kileyc,

Suggest you to look into SumIFs or Sumproduct function ... post the workbook in case of any issues, as suggested by Vinoth. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

**kileyc** · 11-19-2012, 10:54 AM

Hi! Thank you both for your help. I attached a sample spreadsheet with some detail.

**dilipandey** · 11-19-2012, 01:08 PM

First, it looks to see if any number in B:K of each row is above X. If a number is above X then I need to find out if there is a second number in the row above Y. If there is a number above Y then I need to return the min of that number minus X and another number Z.

If I look into you attachment in B6:k6.. then I found that C6 is having value as 3000 which is above x i.e., above 500 and it is also above y i.e. above 300.. Now what ? there could be a first number which can be greater than x and then there again could be a first number which is greater than y.. can you put some results manually to make this clear. I am asking this because I don't want to work on assumptions

thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

**kileyc** · 11-19-2012, 01:38 PM

Thank you again for your help! I'm sorry my example doesn't make much sense and the formula i tried to make in column q is incorrect.

If we use row 7 as an example, in column Q i'd like a to find the first number in row 7 that is larger than x, 500 --- in this case it is 3000. In row R I would like to find the first number in row that is larger then Y, starting to the right of the cell that contained the result of the first formula, so starting at C7 instead of B7. So in this case the result would be 800.

Thank you!

**dilipandey** · 11-20-2012, 01:19 AM

starting to the right of the cell that contained the result of the first formula,

What if the 800 would have been there on the left side of 3000.. then still you searched on the right of 3000 and then the result would had been 8. correct ? thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>