Microsoft Office Forums Sum by month according to percentage and dates
 Register FAQ Search Today's Posts Mark Forums Read

#1
11-26-2022, 09:21 AM
 rammy25 Windows 10 Office 2021 Novice Join Date: Nov 2022 Posts: 5
Sum by month according to percentage and dates

Hey, if anyone can help me with this because I can't manage, I try to make the sum per month for some length that are put by dates in column 50%, 90%, 100%.

There is a formula to calculate this?

Thank you
Attached Files
 TEST.xlsx (23.8 KB, 5 views)
#2
11-26-2022, 12:35 PM
 p45cal Windows 10 Office 2019 Expert Join Date: Apr 2014 Posts: 723

Is this what you're after?:
2022-11-26_193354.png
See attached.
Attached Files
 msofficeforums50046TEST.xlsx (40.0 KB, 5 views)
#3
11-26-2022, 01:26 PM
 rammy25 Windows 10 Office 2021 Novice Join Date: Nov 2022 Posts: 5

It's something like this, but the pivot not returning correct values because for one thing done 100% in november and 90% in august you should have only 10% in november but pivot take 100%
#4
11-26-2022, 02:34 PM
 p45cal Windows 10 Office 2019 Expert Join Date: Apr 2014 Posts: 723

You'd best put values calculated manually in the grid so that we can try to reproduce.
It would also help to put some simple calculations in some cells so we can see how you arrive at some of those values.
#5
11-26-2022, 03:16 PM
 rammy25 Windows 10 Office 2021 Novice Join Date: Nov 2022 Posts: 5

Here is the excel with some formula Sumifs by criteria of dates that I calculate for november but if I go back to october, june I loose track of everything
Attached Files
 TEST.xlsx (25.1 KB, 6 views)
#6
11-27-2022, 07:00 AM
 p45cal Windows 10 Office 2019 Expert Join Date: Apr 2014 Posts: 723

OK. It looks quite complicated! I've not got my head around it yet.
Maybe it would help me if you can you say, in words, what the results intend to show, and perhaps, for a bit of context, what this data represents in the real world.
And maybe take one row (say row 13, index 12) and say what has happened on/by each of the 3 dates?
I'm guessing these dates might be the date by which 50%, 90% and 100% of the lengths (of pipe?, railway?) were completed?

Anyway, another guess, but the results are different from yours:

2022-11-27_135920.png
Attached Files
 msofficeforums50046TESTv2.xlsx (44.0 KB, 3 views)
#7
11-27-2022, 07:57 AM
 rammy25 Windows 10 Office 2021 Novice Join Date: Nov 2022 Posts: 5

Yes, on rows are piping length and on column the progress

for example for row 13(12)
Lenght: 12.61
on 25.08.2022 was done 50% so 6.305 ml
on 26.08.2022 was done 90% from total so 0.9from total lenght: 11.631ml but without the 6.8 ml that was done before so the result is 5.044

so in the future for 100% it remains to be done 1.261 ml

this is an extract from a larger data base that is export like this and I would like to calculate how many meters was done per month
#8
11-27-2022, 09:17 AM
 p45cal Windows 10 Office 2019 Expert Join Date: Apr 2014 Posts: 723

So the last file I attached seems to be correct?
Here's that pivot filtered for just index 12:
2022-11-27_161422.png
I've added grand totals for rows as well so that you can see the total length tallies.
The values for August and October seem to be right too.
#9
11-27-2022, 11:52 AM
 rammy25 Windows 10 Office 2021 Novice Join Date: Nov 2022 Posts: 5

But in the excel if you will put filter 50%, 90%, 100% in november you should have for 100% 175 ml??? I didn't check for grand total if it,s correct but I will check

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post kingmb Excel 2 09-11-2018 01:08 AM Marcia Excel 4 05-30-2018 01:51 AM DBenz Excel 2 03-28-2018 04:59 AM Exhale Excel 3 04-05-2016 03:11 AM madison320 Project 0 06-24-2011 08:15 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 07:24 AM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top