Hi,
I have a large data ranging between 200k-400k row every weekday to analyze.
I need to get result for Column P, Q, R, S, T, U , V, W, X, Y from Row 2 to Row 7201 (sometimes until Row 1441 , 481 ,241 ,121 depend on what kind of analyze is).
Current formula I use is "...
Is there an equivalent for SUMIFS formula in Power Query? I would like to calculate the yellow column in Power Query which includes a cumulative sum for each account for each month and year. Grouping the columns does not work as I want to have both the monthly and the cumulative value in the...
Hello All,
I have been trying to replicate excel Sumifs in Power BI without much luck. I have below excel data and a summarised table. The Table on the right in excel is essentially doing sumifs based on filter selection. For e.g. Sum of Volume for Customer =Cust 1, Product=Hat 1, Plan =A...
I'm stuck! I've been trying to figure this out for hours now. I have the following formula below:
=SUMIFS('July - Present'!O:O,
'July - Present'!A:A,">="&DATE(2021,10,1),
'July - Present'!A:A,"<="&DATE(2021,10,31),
'July - Present'!Q:Q,"",
'July - Present'!R:R,"",
'July - Present'!S:S,"")
The...
Good Moring All!
I am looking to reference a separate table on a separate worksheet in the same workbook. I would like to total all hours worked for one specific job code in the table. The formula is below: What am I missing?
Too few arguments...
Hi,
I have two-pronged question though the most important one will be the 1st bullet and the less important will be the 2nd bullet. The uploaded excel will show the same format for the aggregate data on the top and the different sets of data outlined in black below it. Unsure if sumifs and...
Hello,
I'm looking to sum all of the sales within 2021 from the "All Sales" table into the "Quarterly / Yearly Sales" table M7, based on the date 01/01/2021 date entered in A1.
I was able to get this working with months using EOMONTH, is this possible with years?
If I change A1 to 01/01/2022...
Hi Experts
I'm using a SUMIFS formula that was working fine till today. Now I want a new dimension added to it which I'm not able to figure out. If you may please help.
Required -
If there is some value in Banks[Less] and there is a corresponding Value in Banks[Add] then it should subtract...
I have a summary page with people's names and I am summing up their sales from a given month (worksheet Jan, Feb, Mar, etc.). In those months there are one or more sales recorded in a row. But for some reason starting in the month of May and only on a few select people it no longer gives a sum...
Hi,
I am stuck with trying to get the below dataset into a summary table using formulae (cannot use VBA as it is a marco-free file).
BCDEFGHIJKLMNOPQ2Sample dataset3AccountWeightageJan-21Feb-21Mar-21Apr-21May-21Jun-21Jul-21Aug-21Sep-21Oct-21Nov-21Dec-21TOTALSpecial condition4Acct...
I am creating a template file with formulas so it can be easily shared out across departments. There are a number of categories (such as Staffing, Software, Outsourcing, etc) on this template. I am looking up from a data dump, and there is a column that will contain different (what we call)...
Hi community-
I'm looking for a formula to sum up working hours for various employees , grouped by Optician ID, if they had an active contract within a certain month.
I've played around with SUMIFS, but I get an error message, that it's not a formula...
Hi All,
I have a really dumb question that I can't seem to figure out. I have a SUMIFS formula searching a range of numbers that contain a few #values! errors. Obviously, the SUMIFS result is going to be an error (#values!) for these, and I can't use aggregate since it doesn't have SUMIFS. The...
Hello,
I'm looking to find the profit for sales in a list. Problem is I need to check that both the product and size match. When using a sumifs statement, it would not let me set the sum range to a single cell, it only would accept a range.
Goal: If product & size exist/true, then "(Retail...
I'm using the formula below trying to grab items and dollar amounts (not formatted, data came straight from a csv pull). Only the months June, September, August, and July are showing the sums. The remaining months are showing 0. I'm not sure what's causing this since it came directly from a...
Hello,
I'm looking to sum the sales per month into 1 cell, I need to sum each "Type" that is the same per month.
Example:
01/01/2021 ("Product 1 Type 1" sold 2) & ("Product 2 Type 1" sold 3)
01/02/2021 ("Product 1 Type 1" sold 4) & ("Product 2 Type 1" sold 5)
Total Sold In January 2021: 14...
Hello all! New User here, so bear with me
I'm working on a little pet project, attempting to parse out data from a large date range using smaller date ranges. The goal is to automatically get references given a date range that can be used for math in other cells. Most of this can be done with...
Hello, I have a table I use for sales forecasting, teams fill in the out months and I would like a process to create a table that can be imported into the General Ledger.
Above is the import file, the teams would fill this in. EAch month there could be unique Billing Codes, and the billing...
Hi,
I am looking for a formula to put in cells B2:F7 on Sheet 2.
Notes
- This is a simplified version of the file I am actually working with. The file I am working with has all work-day dates (Mon-Fri) in row 2 on Sheet 1, for 01/01/2021 to 30/06/2022.
- Each Trade Reference is unique, so...
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.
Allow Ads at MrExcel
Which adblocker are you using?
Disable AdBlock
Follow these easy steps to disable AdBlock
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
Disable AdBlock Plus
Follow these easy steps to disable AdBlock Plus
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.