Return average and count from multiple criteria in a list or array of data

**robcosta** · 08-15-2008, 06:57 AM

Hello,

I have a tough one here (for me anyway!) Please see attached.
I have 2 sheets.

I need a formula that will return the correct average and counts from sheet 2 based on specified criteria in the first 2 columns viz Brief # and Duration

On sheet 2 I have indicated what the answers should be (all colour coded) based on the data in sheet2(A:F). I however want the answers on sheet 1 as indicated.

I am sure this is an array formula, but cant seem to figure it out !!

Any help is MUCH appreciated.

Regards

**NBVC** · 08-15-2008, 08:12 AM

See attached...

All formulas are array formulas and must be entered with CTRL+SHIFT+ENTER instead of just ENTER.

Note: I changed you duration entries into actual numeric strings so that I could compare against column A.

I am also unsure why/how there is no distinction made between Scotland and Factual.

**robcosta** · 08-15-2008, 09:21 AM

Hello,

Thanks so much, you are correct, I have adjusted the file slightly to include a 3rd match criteria - viz supplier. Please see attached.

Thanks again for excellent help!

**NBVC** · 08-15-2008, 09:34 AM

See attached for revisions....

**robcosta** · 08-18-2008, 03:37 AM

Hi,

Thanks again, I have tried to apply it to the huge list that I have (25000 rows and 10 columns) and it is returning 0 for all items. I am proably being really thick, but are there any other rules I should be aware of other than ctrl+shift+enter?

I have looked at the formats and made sure the column header match the downlaod sheet etc

Sorry for being a pain, but please could youlet me know what I did wrong on the attached (highlgihted in yellow)

Thanks

**NBVC** · 08-18-2008, 11:56 AM

It seems you've changed the format of column B in the Model tab?

Anyways, you don't need the Time() function anymore...

Try this in H3:

Please Login or Register  to view this content.

You will need to make the same adjustments for other formulas....

**robcosta** · 08-22-2008, 03:18 AM

Hi,

Thanks, that has worked wonders! Could I ask another question please?

It is taking about 10 minutes on average to calculate everytime I save or open the file. I have core duo pentium and it is using both processors. I have tried about 4 computers and it take longer on some even with large available RAM etc. Is this because I have such a large array (27000 rows and 14 columns). The array formula is nesting 6 times, is it just too much for Excel to handle?

Thanks again?