LinkBack URL
About LinkBacks
Hello,
I can't connect Sumproduct and Countifs in my example that you can find in the attachment.
Within the file I've described the problem.
Therefore, if someone can look into the file, I would appreciate it.
Thanks.
Sumproduct + countifs
Hello,
I can't connect Sumproduct and Countifs in my example that you can find in the attachment.
Within the file I've described the problem.
Therefore, if someone can look into the file, I would appreciate it.
Thanks.
Date in B1 was wrong
01/02/2016 not 12/02/2016
Oooo, I see. Thanks!
Last edited by toci; 03-27-2016 at 10:29 AM.
Change your date in B1 to 01/02/2016 (as per my previous note)
Again, I put wrong info in A1. I mean, the Date is the end of the Week, not just the Month.Originally Posted by JohnTopley
If you just want the results for February (or any month) then with the correct date in B1 (which should be 01/mm/2016 where mm is required month) you will get the correct results. You don't need the week number.
In B1 I need to put the END Date of the Week as I produce report on a Weekly basis.
Therefore, tomorrow e.g. I'm gonna change date in B1 like 2/19/2016 and then I need AVERAGE for all Weeks in February. How to pull out Date from B1 like month and compare it with Date in range B4:BC4?
So, I am not able to put Date in B1 like 2/1/2016 (February 2016).
You need to understand your current formula:
=SUMPRODUCT((MONTH($B$4:$BC$4)=MONTH($B$1))*($B6:$BC6))/COUNTIFS(B4:BC4,B1,B6:BC6,">0")
SUMPRODUCT sums the data where month=2 (in this case) so if any week is zero it does not matter i.e. 865 + 802 + 900 + 0 for February
.
COUNTIF checks B1 against B1:Bc4: the latter have dates of 1st of month for every month NOT the individual week dates.
You then check if data if a row (6 in this case) is > 0 so if only 3 weeks in February have data so COUNTIF will return 3.
SUMPRODUCT will only have add " weeks data (as other are zero) so you will get correct average.
If I make last week in Feb 1000 for product AAA, the total for February is 3567, divide by 4 = 891.75
Alternative ..
.=AVERAGEIFS($B6:$BC6,$B5:$BC5,">=" &$B$2,$B5:$BC5,"<=" &$B$3)
where B2 = "Week 6" and B3 = "Week 8"
If the date in B1 needs to be (e.g.) the current date, then you could use SUMPRODUCTfor both parts of your formula, as:
=SUMPRODUCT((MONTH($B$4:$BC$4)=MONTH($B$1))*($B6:$BC6))/SUMPRODUCT(--(MONTH($B$4:$BC$4)=MONTH($B$1)),--($B$6:$BC$6>0))
My original formula was =SUMPRODUCT((MONTH($B$4:$BC$4)=MONTH($B$1))*($B6:$BC6)) and now I want to find AVERAGE for Month to Date for Current month.
In cell B1 I can't put Date like you told me 01/02/2016 as I must to put there Week END Date as I have also formulas which are connected on number of Week. That means for February in B1 I will have next dates:
2/12/2016, 2/19/2016, 2/26/2016 and so on.
So, if my formula isn't good can you tell me the new one?
Thank you.
The number of Week in cell B2 is very important! If I change the Date e.g. in B1 1/12/2016 (January 12, 2016) then I need Average of the first 3 Weeks in January.
Last edited by toci; 03-27-2016 at 12:08 PM.
Use the AVERAGEIFS formula where you specify a start and end period. post #9
If I knew how, I wouldn't ask.
Doesn't work!
Sorry it does: I tested on your data.
See attached .. result in "green" cell
Your original formula doesn't mention B2!
My modification to it #10, will give the Month to date Average provided that the date in B1 is in the Month of interest. Does that work for you ?
I've explained with BLUE color. Still this is not what I want to calculate.
So you dont want Average month to date then, as you said above (#11 I think), as your blue highlight doesn't include the last week! If you want a part periods figures, then Johns AVERAGEIFS is the way to go
I have offered several solutions: if you persist in using a date then make B3 the start week and change the AVERAGEIFS accordingly. You need to define a start and end date/week number so in your sample B3 = Week 6.
It is not that difficult!
If you had checked my response properly you would have worked out I included weeks 6 to 9 (not 8) with Week 9 containing 1000. Hence the result of 950!!!
Month to Date means if I have in B1: 2/19/2016 that Date is the END of Week 8. B1 refers that we are in February. So, I need to find AVERAGE of All Weeks till Week 8 for that Month. I have tried AVERAGEIFS for January and can't get AVERAGE that I need.So you dont want Average month to date then, as you said above (#11 I think), as your blue highlight doesn't include the last week! If you want a part periods figures, then Johns AVERAGEIFS is the way to go
I am not sure if we understand each other?
Sorry, but how did you get AVERAGE of 950 if you put value 1000 in J6. In this case I need average of Week 6 + Week 7 + Week 8 + Week 9, and that is 892? Still cant understand you.
See table in A16 onwards:
Nope man, this is very close to solution but if I change the date in B1 and put e.g. 1/12/2016 (January, 12) get error and it's OK. I change Week in B3, put Week 1 and it's work but if I keep Week 1 in B3 and then, again, change the Date in B1 to e.g. 2/12/2016 (February, 12) don't get correct AVERAGE. I would like to avoid manually changing starting Week. Is it possible?
Thank you very much on patience.
Assuming 12/02/2016 is Week 7, then I don't have any problem with the averages (with B3=Week 1)
For AAA Weeks 1 to 7 SUM(B6:H6) =4818 and average=4818/7 = 688.29
This is the result I get in B18.
Date in B1=12/02/2016
With B1=01/01/2016 and B3= Week 1 the average is the value for 1st week in January.
That's why we don't understand each other. If we put in B1 12/2/2016 (February, 12) it's indicate Week 7 and that's correct. But B1 indicate that we are in February (don't need data from January) and I need AVERAGE for Current month (in this case it is February) but as I said, I need current Month to date Average, so that should be only (Week 6 + Week 7) and to get Average of 834 not 688,29.
My final offer (as your original formula worked fine if you only wanted to sum a single month!)
in B3
="Week"&" "&WEEKNUM(EOMONTH($B$1,-1)+1,16)
the highlighted will give 1st of the month in B1
Your final offer is like a CHARM! Thank you sir!
This is what I need!
Cheers!
A somewhat frustrating journey!
If you now have your solution, can you please mark thread as solved ("Thread Tools" at top of first post)
Month to Date is a standard term used in Accountancy Which means the latest estimate available for the month/period in question.
So..... if you have figures completed for 3 weeks then the total of these would be MTD.
It appeared that you understood this, because your initial formula was calculating an average for the 3 completed weeks of period 2, namely 855.67 (6,7 and 8), and NOT P2 till the date in B2, which would be just Weeks 6 and 7.
Turning to your comments the AVERAGEIFS, your computer can only work with the instructions its given. The reason you have an average of 950 in your attachment is because you have changed the week selection from John's Weeks 6-9 (average 891.75) to only Weeks 8-9 (average 950) both of which are correct with the parameters provided.
My advice to you is to step back and think through where you want to get to, and make sure you understand how the functions your using actually work. John has done a lot of explaining to help you with this in his posts. This will (hopefully) mean that you won't keep adding/changing your data and/or expected results without keeping the formulae in line. As a footballing friend used to say:
If you keep moving the goalpost, we'll never get the ball in the net
Last edited by Hercules1946; 03-27-2016 at 04:18 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks