I'm trying to figure out the rest if this formula and have tried several things, without success.
=SUMPRODUCT(D:D,--ISNUMBER(MATCH(C:C,{"July","August","September"},0)))
The above function finds those cells in Column C with the texts "July", "August", "September" and sums the figures beside them in Column D. Now I need to finish the formula, dividing it by the total number of such occurrences to obtain an average figure. There are blank cells in this worksheet.
Can anybody help me? Thanks.
Hi and welcome to the forum
1st, try and avoid using entire columns (or rows) when using sumproduct(), it will start to slow your file down - try and restrict it to a range 2-3 times larger than you thing you will need.
2nd, you could probably use sumifS() and countifS() if you could use real dates instead of just month names
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
I could follow the first, but not the second suggestion because the month names are text which are populated into those cells by another VBA code.
Maybe this...
=SUM(SUMIF(C:C,{"July","August","September"},D:D))/SUM(COUNTIF(C:C,{"July","August","September"}))
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
If you were using real dates instead of text "dates", you could use something like this...
=SUMIFS(D:D,C:C,">=7/1/14",C:C,"<=9/30/14")
and then...
=COUNTIFS(C:C,">=7/1/14",C:C,"<=9/30/14")
That is the ticket! Thank you very much, Mr. Valko
You're welcome. Thanks for the feedback!
If your question has been solved please mark the thread as being solved.
In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.
Oops! Hmm. Mr. Valko, the formula is giving me a DIV/0 Error because there are blank cells. Is there a work-around for that?
Empty cells should not cause an error unless ALL of the cells are empty.
You'll get the #DIV/0! error if the COUNTIF = 0.
You could get an incorrect result if cells in column D are empty but the corresponding cells in column C contain one of the criteria months.
Which column has the empty cells?
You are correct, I had all empty cells in one section. Much appreciated.
Ok, you can prevent the #DIV/0! error with this version:
=IFERROR(SUM(SUMIF(C:C,{"July","August","September"},D:D))/SUM(COUNTIF(C:C,{"July","August","September"})),"")
That'll return a blank instead of any errors.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks