Sum(if or Count(if based on 3 different criteria

The spreadsheet I am using was created back in the 1990s. unfortunately, the formula stop working when I was trying to update it by incorporating some additional functionality.

The formula needs to do
-1st it check whether the value (integer value) in Cell B10 greater than the value (integer value) in Cell A1.
If it does then the it will enter the result in E1(where this formula entered) blank
If the value in Cell B10 is not greater than the value in Cell A1

-2nd It pick all the cell from C1 through C10 which has a value of 'Y' in them.

3rd Then it will jump to column D and pick all the cell from D1 to D10 with values that has the Value 'Y' in the corresponding cell C1 through C10.
Then it pick the very 1st value from cell D1 to D10 (with corresponding Cell C1 to C10 with value 'Y') compare this value with the other values from cell D1 to D10

4th- Finally, it will count how many of the values are greater than the very 1st value in cell D1 to D10.
It would do the same for the second, third, fourth,....values in cell D1 to D10.

Below is the existing formula, please help how to re-write this formula using MS Excel version 2007 and up (in particular in 2010)

=IF(B10>A1,(""),SUM((C1:C10=("Y"))*(D1:D10<>(""))))

I tried to re-write it in two different ways as shown below including the result I got

=COUNT(IF(B10<=A1,IF( C1:C10 = "y",IF(D1:D10<>"", D1 < D1:D10,("")))))
Result was 0

=SUM(IF(B10<=A1,IF( C1:C10 = "y",IF(D1:D10<>"", D1:D10,("")))))
Result was #VALUE!

Please Help!

I'm not really sure about your 4th condition, but try this array* formula to count the number of cells:

=IF(B10>A1,"",SUM(IF((C1:C10="Y")*(D1:D10<>"")*(D1:D10>D1),1,0)))

*Note that an array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE), instead of the usual <Enter>.

Hope this helps.

Pete

Pete,

Thank you for your prompt reply.
I didn't know that I should be committing array formula using the key combination of CSE.

For simplicity I used Cell A through D but the actual cells in my spreadsheet with the modified cells looks like the one shown below.

=IF(Z19>$A$20,"",SUM(IF(('Test Results (2)'!$E22:$E275="Y")*(Z$22:Z$275<>"")*(Z22:Z275>Z22),1,0)))

Unfortunately, the result was, using formula,#DIV/0!

Just to make it clear,
1st-The function checks the two main criteria are met (Z19 <= A20 & it look into another worksheet, 'Test Results (2)'!, in the same workbook Cell E22 to E275 has "Y")
2nd- The function picks the first value from cell Z22:Z275 with the corresponding cell in E22:E275 with the "Y"
3rd - The function will compare this first value in cell Z and compare it with all the values coming with corresponding Cell E = "Y" after it in the same column and counts how many of these values are above this first value in cell Z.
4th- The function will go and find the second value in Cell Z with the corresponding cell E="Y" and compare it with all the values in cell Z the same way and counts how many of these values are above this second value in cell Z.
5th- the function will do the same all the way including the last value in cell Z with Corresponding cell E="Y".

Sweep,

Thank you for your prompt reply.
I didn't know that I should be committing array formula using the key combination of CSE.

For simplicity I used Cell A through D but the actual cells in my spreadsheet with the modified cells looks like the one shown below.

=IF(Z19>$A$20,"",SUM(('Test Results (2)'!$E22:$E275="Y")*(Z$22:Z$275<>"")*(Z22:Z275>Z22)))

Unfortunately, the result was, using formula,#DIV/0!

Just to make it clear,
1st-The function checks the two main criteria are met (Z19 <= A20 & it look into another worksheet, 'Test Results (2)'!, in the same workbook Cell E22 to E275 has "Y")
2nd- The function picks the first value from cell Z22:Z275 with the corresponding cell in E22:E275 with the "Y"
3rd - The function will compare this first value in cell Z and compare it with all the values coming with corresponding Cell E = "Y" after it in the same column and counts how many of these values are above this first value in cell Z.
4th- The function will go and find the second value in Cell Z with the corresponding cell E="Y" and compare it with all the values in cell Z the same way and counts how many of these values are above this second value in cell Z.
5th- the function will do the same all the way including the last value in cell Z with Corresponding cell E="Y".

If any one of the cells in E22:E275 on the Test Results sheet or in Z22:Z275 contains an error like #DIV/0, then the formula will also return that error. You should amend the formulae in those ranges by surrounding them with IFERROR(your_formula,"")

Hope this helps.

Pete

Pete,

Thank you again.

You're absolutely correct the problem was as you stated above.
I shrinked my array to cell with values and it works fine.

I'm going to try the IFERROR that you suggested.

Thanks!

Pete,

Thank you again.

You're absolutely correct the problem was as you stated above.
I shrink my array to cell with values and it works fine.

I'm going to try the IFERROR that you suggested.

Thanks!

Glad to be of help.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete