Count if formula with nested components... please help

[Joe Spamalot]

Hello saviour

I'm a bit of a novice in excel and have been racking my brain and the internet on this one. Here's the data set

[TABLE="width: 686"]
<tbody>[TR]
[TD="align: left"]B
Rain days[/TD]
[TD="align: left"]C
Apr[/TD]
[TD="align: left"]D
May[/TD]
[TD="align: left"]E
Jun[/TD]
[TD="align: left"]F
Jul[/TD]
[TD="align: left"]G
Aug[/TD]
[TD="align: left"]H
Sep[/TD]
[/TR]
[TR]
[TD="align: left"]We[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]Th[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]Fr[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]0.8[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]Sa[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]0.3[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]Su[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]3.4[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]Mo[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3.4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.8[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]Tu[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0.4[/TD]
[TD="align: right"]9.6[/TD]
[TD="align: right"]7.6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: left"]We[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.8[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: left"]Th[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.6[/TD]
[TD="align: right"]0.1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: left"]Fr[/TD]
[TD="align: right"]14.6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0.4[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: left"]Sa[/TD]
[TD="align: right"]3.8[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1.4[/TD]
[TD="align: right"]0.1[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]10.4[/TD]
[/TR]
[TR]
[TD="align: left"]Su[/TD]
[TD="align: right"]9.2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]6.8[/TD]
[TD="align: right"]1.2[/TD]
[/TR]
[TR]
[TD="align: left"]Mo[/TD]
[TD="align: right"]0.8[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1.4[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: left"]Tu[/TD]
[TD="align: right"]0.2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3.4[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: left"]We[/TD]
[TD="align: right"]0.4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: left"]Th[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.2[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: left"]Fr[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5.4[/TD]
[TD="align: right"]6.3[/TD]
[/TR]
[TR]
[TD="align: left"]Sa[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]10.2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2.3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]18[/TD]
[/TR]
[TR]
[TD="align: left"]Su[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]21.8[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1.2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4.4[/TD]
[/TR]
[TR]
[TD="align: left"]Mo[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]29.6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]30.7[/TD]
[TD="align: right"]11.2[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: left"]Tu[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]10.4[/TD]
[TD="align: right"]5.8[/TD]
[TD="align: right"]0.3[/TD]
[/TR]
[TR]
[TD="align: left"]We[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1.4[/TD]
[TD="align: right"]0.1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: left"]Th[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2.8[/TD]
[TD="align: right"]10.4[/TD]
[TD="align: right"]20.8[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: left"]Fr[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4.6[/TD]
[TD="align: right"]0.1[/TD]
[TD="align: right"]4.2[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: left"]Sa[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5.7[/TD]
[TD="align: right"]0.1[/TD]
[TD="align: right"]14.2[/TD]
[TD="align: right"]0.8[/TD]
[/TR]
[TR]
[TD="align: left"]Su[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.6[/TD]
[TD="align: right"]16.3[/TD]
[TD="align: right"]0.1[/TD]
[TD="align: right"]0.1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: left"]Mo[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]10.4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.2[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: left"]Tu[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.2[/TD]
[TD="align: right"]0.1[/TD]
[TD="align: right"]1.4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: left"]We[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: left"]Th[/TD]
[TD="align: right"]0.1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4.2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: left"]Fr[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7.4[/TD]
[TD="align: right"]2.4[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: left"]Sa[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.1[/TD]
[TD][/TD]
[TD="align: right"]1.8[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: left"]Su[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]12.2[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: left"]Mo[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: left"]Tu[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: left"]We[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: left"]Totals[/TD]
[TD="align: right"]44.1[/TD]
[TD="align: right"]66.3[/TD]
[TD="align: right"]53.8[/TD]
[TD="align: right"]104[/TD]
[TD="align: right"]106.6[/TD]
[TD="align: right"]45.4[/TD]
[/TR]
[TR]
[TD="align: left"]number of rain days[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: left"]Average of rain days[/TD]
[TD="align: right"]4.4[/TD]
[TD="align: right"]8.3[/TD]
[TD="align: right"]3.6[/TD]
[TD="align: right"]4.5[/TD]
[TD="align: right"]5.6[/TD]
[TD="align: right"]5.7[/TD]
[/TR]
[TR]
[TD="align: left"]Wet days[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The formula which is returning a 0 in Wet days is:

=COUNTIF(C16:C51, VLOOKUP( OR("Mo", "Tu", "We", "Th", "Fr"),B16:H51, 2, FALSE) >C54)

What I want is to add up the number of rain days that occurred on a week day that was above the average for the rain days in that month

Help greatly appreciated

Cheers

Joe

 

[Joe Spamalot]

Found the solution to this if it helps anyone else. It is

=SUM(COUNTIFS($A$2:$A$37,{"Mo","Tu","We","Th","Fr"},B2:B37,">"&B40))

Cheers

Joe