Excel Sumproduct

B

Brian31273

Joined
Nov 22, 2017
Messages
1
Reaction score
0
I have a specific formula that counts the dates by month/year (B718). I have 9 total as you can see below.... In another column I have time spent on those dates. I need to find the average time over those dates with time spent. I actually have the formula working but the total dates e.g. 9 was my divider... So, if the column with time spent had empty/blank cells my average would calculate incorrectly since the total dates didn't match the total cells with time spent... As you can see the picture below shows the two columns (dates) and (spent hours)... there is blank cells. Here is the formula that works if there is no blank cells.... My column B is hidden, but column C shows the dates

=SUMIF(B:B,B718,H:H)/COUNTIF(B:B,B718)
upload_2017-11-22_12-37-58.png

I was thinking of using sumproduct or countblank and minus the sum from the number of dates found, this number would be the revised number to divide my total hours to find the average...

Please help,
Brian
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel Excel calculate pay based on rate per minute 7
Excel Average dates help 0
Excel Excel 2007 Formula Help Needed 1
calculate total hours based on dates 5
Trailing 30 days sum calculation with excel 1
I copied Visible Cells Only, but it pastes only the first cell 1
counting dates using SUMPRODUCT 4
counting dates (months) using SUMPRODUCT 1

Top