Sumproduct or count problem

[JV0710]

Hi

Please can I get some help with the following query:

I have the following formula in my spreadsheet

=SUMPRODUCT(--(X7:X1000=AH8)*(W7:W1000=AH7)*ISNUMBER(N7:T1000))

I need to add another condition:
In the area that I want to count columns (N7:T1000), I only want to count in the columns where the cells in each column have more than 5 cells with numbers in the column and that satisfy the other conditions in the formula:

For example: If in column P of my area ( N7:T1000), where (X7:X1000=AH8) and (W7:W1000=AH7) there are only 3 cells with numbers, then column P must NOT be included in the count. the area to include in the count should then be (N7:07) & (Q7:T7)

I hope that makes sense

I would appreciate any help please

Thanks

Joe

 

[barry houdini]

Hello Joe,

Try this "array formula"

=SUM(TEXT(MMULT(TRANSPOSE((X7:X1000=AH8)*(W7:W1000=AH7)*ISNUMBER(N7:T1000)),ROW(X7:X1000)^0),"[<=5]""0"";0")+0)

confirm with CTRL+SHIFT+ENTER

 

[JV0710]

Hi Barry

Thanks for the reply . . .

I tried your formula but it still counts the cells in columns that have less than 5 cells with numbers in them.
FOR INFO --- In my Actual spreadsheet columns N and T have less than 5 cells that have numbers in them ( where conditions are met) , so they need to be excluded from the count.

Thanks

Joe

 

[barry houdini]

OK, Joe, based on my understanding of what you want to do then that formula seems to work for me.

See if I understand OK.....

Let's say you have 10 rows only where the two criteria for columns W and X are satisfied....and in those same 10 rows in column N you have only 3 numbers....but in column P you have 6 numbers..then the 6 would be included in the total but the 3 wouldn't.....that's what my suggested formula does.

You could get the same answer by using a helper row, e.g. in N1 use this formula copied across to T1

=SUMPRODUCT(($X7:$X1000=$AH8)*($W7:$W1000=$AH7)*ISNUMBER(N7:N1000))

and then use a SUMIF formula on that range to get the final total

=SUMIF(N1:T1,">5")

That should give the same result as my single formula

Note that assumes that exactly 5 doesn't count - if you want exactly 5 to count then change >5 to >=5

 

[JV0710]

Hi Barry

Sorry I took so long to respond

I have worked it out with your formula

Thank you very much for your help

Regards

Joe