# Count with several criteria and Average with complex criteria

1. ## Count with several criteria and Average with complex criteria

Hi everyone!

I'm currently working on a worksheet, using Excel 2003 and I want to make it as much automated as it can (more time I spend now, less I spend on it later!).
I already spent mmuch time on it, but my excel skills are quite limited. When I see what you guys are able to do, it just amazes me!
Attached is the spreadsheet. It's quite big (and it's gonna get bigger), but there are several formulas I am looking for.

First, I want to calculate the average allocation (column G) by lead (column E).
I know how to count the different deals by leads, but I can't find a way to calculate the average. I think it's good to use the "participated" column (C) with "Yes" and "No". I've been using it for other formulas.
And as far as I've been looking into it, it looks like it's got to be a rather big formula. Any idea ?

My second question is how to calculate the average moves in prices (table from column Y to AE). But I want a specific average:
if the move in T+1 is positive (col Z), I want the average of T+2 (Col AA) for those stocks that were positive in T+1.
Something that would sound like: =AVERAGE([stock in T+2] IF[stock move in T+1]>0).
Let me know if that isn't clear (rather precise/complex question).

These two formulas would save me a lot of time and work (otherwise I'll have to make these calculation manually).
And I believe it's a rather good challenge for you guys !
Let me know if you have any good guesses.
Best,

Alash

2. ## Re: Count with several criteria and Average with complex criteria

in 2003, you will need to use a combination of Functions to get the Averages.

you can either use an array formula that looks like this:

=AVERAGE(IF(Z7:Z54>0,AA7:AA54))

which you need to confirm with CTRL+SHIFT+ENTER not just ENTER...

or you can use the SUMIF and COUNTIF functions like this:

=SUMIF(Z7:Z54,">0",AA7:AA54)/COUNTIF(Z7:Z54,">0")

which is confirmed only with ENTER

Does that help?

3. ## Re: Count with several criteria and Average with complex criteria

Very helpful indeed!! And I thought it was going to be a 3 ligns formula.
Thanks a lot!
Any idea on the first question?

I have another question concerning the average then: how could I calculate the average as above, but only for stocks that are positive on T+2 ?
That is calculate the average of stocks positive in T+1 and in T+2?
Thanks a lot anyway for your help, you saved me a lot of time!

4. ## Re: Count with several criteria and Average with complex criteria

In the first question, you say you know how to count what you want...

Well you should be able to use the same AVERAGE(IF()) or SUMIF/COUNTIF formulas just adjusting the ranges.

If you are including multiple conditions you could use SUMPRODUCT/SUMPRODUCT instead of SUMIF/COUNTIF

eg

=SUMPRODUCT(--(A1:A10="X"),--(B1:B10="Y"),C1:C10)/SUMPRODUCT(--(A1:A10="X"),--(B1:B10="Y"))

where you are counting the number of Xs and Ys in the same rows and averaging column C

=AVERAGE(IF(Z7:Z54>0,IF(AA7:AA54>0,AA7:AA54)))

or

=SUMPRODUCT(--(Z7:Z54>0),--(AA7:AA54>0),AA7:AA54)/SUMPRODUCT(--(Z7:Z54>0),--(AA7:AA54>0))

5. ## Re: Count with several criteria and Average with complex criteria

Hey, thanks a lot for your help, it works perfectly.
Have a good day!

6. ## Re: Count with several criteria and Average with complex criteria

Great. Happy it worked.

How to mark a thread Solved
Go to the first post
Click edit
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1