COUNTIF & SUM

[GB]

Hi,
in my attached workbook I have a some conditional formatting in every second column. The conditional formatting is checking if each cell is a greater value than the one to the right of itself. If TRUE then highlight green.

In cell CW2 I want to see if there is a shorter/smarter way of counting the number of cells which are TRUE A2>B2, C2>D2, E2>F2 all the way thru to CU2>CV2. In this example only A2>B2 are TRUE so the result should show 1.

In cell CX2 for those conditions which are TRUE (as per above) I also want to sum the difference between each pair which are TRUE. In this example A2-B2.

What do you suggest for efficient formula for these two calculations rather than a very long formula in both cases? Appreciate any suggestions.

regards
GB

 

[Mike H..]

Hi,
in my attached workbook I have a some conditional formatting in every second column. The conditional formatting is checking if each cell is a greater value than the one to the right of itself. If TRUE then highlight green.

In cell CW2 I want to see if there is a shorter/smarter way of counting the number of cells which are TRUE A2>B2, C2>D2, E2>F2 all the way thru to CU2>CV2. In this example only A2>B2 are TRUE so the result should show 1.

In cell CX2 for those conditions which are TRUE (as per above) I also want to sum the difference between each pair which are TRUE. In this example A2-B2.

What do you suggest for efficient formula for these two calculations rather than a very long formula in both cases? Appreciate any suggestions.

regards
GB

Hi,

have a look at you workbook columns CW & CX

The formula are for the count

=SUMPRODUCT(--(A2:CU2>B2:CV2)*(MOD(COLUMN(B2:CV2),2)=0))

and the sum

=SUMPRODUCT(--(A2:CU2>B2:CV2)*(A2:CU2-B2:CV2)*(MOD(COLUMN(B2:CV2),2)=0))

EDIT. Note the -- in both formula isn't necessary, I put it in while developing the formula and forgot to take it out.

 

[GB]

Hi Mike,
that is perfect. I appreciate your prompt and helpful reply
regards
GB

 

[Mike H..]

Hi Mike,
that is perfect. I appreciate your prompt and helpful reply
regards
GB

Glad that worked for you, you might now like to LIKE my response.

 

[Somendra Misra]

Hi GB,

See the file for another formula solution.

Regards,

 

[GB]

Hi Somendra,
thank you for another good solution.
Appreciate your help.
regards
GB