Combining Average if and sumproduct formulas

Kmitchell

Active Member
Joined
Feb 27, 2007
Messages
361
Office Version
  1. 365
Platform
  1. Windows
I have two formulas that work independently but I need to combine and having some difficulty.

Column E sits account names, if it says "FTEs or Headcount" I want it to do an average if formula, otherwise I want it to do a sumproduct formula.

Below is the average if array which works on its own.
AVERAGE(IF($F$4:$Q$4>=$S$2,IF(($F$4:$Q$4<=$S$3),$F566:$Q566))))

And below is the sumproduct which also works on it's own
SUMPRODUCT(--($F$4:$Q$4>=$S$2),--($F$4:$Q$4<=$S$3),$F2416:$Q2416)

I need to be able to combine the two to say if A2 = $E$1 (headcount), or A2 = $E$2 (FTEs) in the cell than do the Average(if formula, if not then do the sumproduct formula.

Any suggestions?
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
This work?

=IF(OR(A2=$E$1,A2=$E$2),AVERAGE(IF($F$4:$Q$4>=$S$2,IF(($F$4:$Q$4<=$S$3),$F566:$Q566))),SUMPRODUCT(--($F$4:$Q$4>=$S$2),--($F$4:$Q$4<=$S$3),$F2416:$Q2416))
 
Upvote 0
Thank you.

It's not currently working. For those that it = $E$1 or $E$2 it gives me 0, all others I get #VALUE!. I did make into an array but that didn't change the outcome.
 
Upvote 0
You will have to array enter it. How big is your sheet? You are looking for an average in row 566 or a sum in row 2416? Is that correct? Formula works ok so im presuming its whatever is housed in the cells that is causing your problem.
 
Upvote 0
You will have to array enter it. How big is your sheet? You are looking for an average in row 566 or a sum in row 2416? Is that correct? Formula works ok so im presuming its whatever is housed in the cells that is causing your problem.

Yes I did array enter, there are 19799 rows currently but this could grow month over month. I am looking for an average when it is an FTE or headcount account and looking for it to sum when it's all other accounts.

This formula below with array enter gives me #VALUE! but it should be $414,000.

=IF(OR($E16=$E$1,$E16=$E$2),AVERAGE(IF($F$4:$Q$4>=$S$2,IF(($F$4:$Q$4<=$S$3),$F577:$Q577))),SUMPRODUCT(--($F$4:$Q$4>=$S$2),--($F$4:$Q$4<=$S$3),$F16:$R16))

I must be missing something.
 
Upvote 0
Which part is failing? Does E16=E1 or E16=E2? If so what is in F577:Q577. If not what is in F16:R16?
 
Upvote 0
Ah ive just seen the sumproduct part is incorrect. Your arrays need to be the same size. F:Q F:Q F:R wont work.
 
Upvote 0
@Kmitchell

Are you not averaging and summing the same range (whichever appropriate) under the same conditions?
 
Upvote 0
Thank you for that correction! That worked perfectly. Thank you for your time and patience!
 
Upvote 0
In your case averageifs and sumifs would be the better option if you are calculating 20000 rows. They are fairly easy to understand formulae presuming you are on 2007 or later.
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top