# SUMPRODUCT with AVERAGE, is this possible?

#### lrusso

##### New Member
Hi

I am not really sure if this is possible, but I'm hoping someone can help or possibly give me another option. Just fyi though, I have no control over the setup of the datasheet. I am just creating a report from the data. The report pulls the data from the company-shared datasheet.

I have a datasheet where I need to subtract the number of days between two dates (start date and end date). This tells me how many days it took to complete each project. Then, I need to average the number of days with both a regular average and a median avg. I need the formula to not count zeros within the results that are being averaged.

My problem is that I'm not sure I can subtract and then average the result within the same formula? Do I need to use a SUMPRODUCT? and if so, how do I make it average the results?

Here is thought would work. However, the results don't come close to my manual test.

=AVERAGE(('Test Data'!\$C\$2:\$C\$65535-'Test Data'!\$S\$2:\$S\$65535))/COUNTIF('Test Data'!S2:S65535:'Test Data'!\$C\$2:\$C\$65535,">0")

Then I was assuming I could us MEDIAN to get the result as well. of course, thats not working either

Any thoughts? I'm definitely willing to try any suggestions

Thanks so much!

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

#### barry houdini

##### MrExcel MVP
Is start date in column C and end date in S? Try

=AVERAGE(IF('Test Data'!\$S\$2:\$S\$65535-'Test Data'!\$C\$2:\$C\$65535>0,'Test Data'!S2:S65535-'Test Data'!\$C\$2:\$C\$65535))

confirmed with CTRL+SHIFT+ENTER

and do the same again with MEDIAN replacing average

#### lrusso

##### New Member
Yay!!!!!!!

IT WORKED!!!!!!! I'm so happy!!! Thank you, thank you thank you!!!!

Replies
1
Views
173
Replies
1
Views
490
Replies
9
Views
201
Replies
1
Views
205
Replies
2
Views
124

1,191,385
Messages
5,986,314
Members
440,017
Latest member
vasanrajeswaran

### 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.

### Which adblocker are you using?

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

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