Hey All!
I have a problem with an array / CSE formula. Hope you can help.
The below works just fine, but I'm having problems with COUNT(IF and AVERAGE(IF
I've entered all array's with Ctrl Shift Enter.
=SUM(IF('2009_3-4-w'!$T$1:$T$2000>"",IF('2009_3-4-w'!$V$1:$V$2000="Europe",IF('2009_3-4-w'!$A$1:$A$2000="Blue",'2009_3-4-w'!$O$1:$O$2000))))
Col. T contains text, Product A, Product B, Product C, Product B -- if there's any text in here i want that row to be included in the average calculation / or count; but there may not be a number of average or calc in Col. O. (there may be a blank or there may be NA, or numbers, positive and negative)
Col. V contains Regions, US / Europe
Col. A contains Colours, Blue / Red
Col. O contains numbers, 6,7, -6, -12, and NA and BLANKS (i think this may be causing a problem?)
COUNT(IF overstates the number of items in column O, and AVERAGE(IF does not average correctly. There's quite a lot of data (900 rows or more), if I use autofilter I can select the range and grab the correct counts and averages, but not via an array formula, any ideas?
Many thanks,
B.
Bookmarks