COUNTIFS help

**alexei84** · 05-25-2009, 04:50 PM

Hi,

I'm not sure if this is possible, but I'm trying to create a formula that will count if the average of three cells is greater than x and the average of the next three cells is greater than y.

I could create a row/column next to the data set to work out the averages, but ideally i'd have the flexibility to adjust the number of cells that are being averaged.

To help clarify, this is one data set:
2,6,2,7,7,3,1,2,2,13,11,1,1,2,2,19,1,0,0,3,2,3,1,2,1,8,2,0,0,7

A formula I would want to create would count the number of instances three cells averaged above 5 when the previous three cells averaged above 4.

Any help would be greatly appreciated.

**contaminated** · 05-25-2009, 04:57 PM

Maybe this should work for you

=IF(AND(AVERAGE(A1:A3)>x,AVERAGE(B1:B3)>y),...

**alexei84** · 05-25-2009, 04:58 PM

Sorry, i didn't think to upload a spreadsheet. Here it is

**contaminated** · 05-25-2009, 05:02 PM

What means these red numbers?

**alexei84** · 05-25-2009, 05:04 PM

the colours are irrelevent - please ignore

**Domenic** · 05-25-2009, 06:33 PM

Are you comparing...

B1:D1 with C1:E1

C1:E1 with D1:F1

D1:F1 with E1:G1

...and so on? If so, try...

=SUMPRODUCT(--(SUBTOTAL(1,OFFSET(B1:AE1,,ROW(INDIRECT("1:"&COLUMNS(B1:AE1)-3))-1,,3))>4),--(SUBTOTAL(1,OFFSET(B1:AE1,,ROW(INDIRECT("1:"&COLUMNS(B1:AE1)-3)),,3))>5))

or

=SUMPRODUCT(--(MMULT((SUBTOTAL(1,OFFSET(B1:AE1,,ROW(INDIRECT("1:"&COLUMNS(B1:AE1)-3))-{1,0},,3))>{4,5})+0,{1;1})=2))

Note, however, MMULT will return #VALUE! if the output of cells exceeds 5,460 for PC or 4,095 for the Mac. If this is the case, download and install the free add-in, Morefunc.xll, and use MMULT.EXT instead. Unfortunately, though, the add-in is not compatible with the Macintosh computer.