1. L

    Sum over an array based on date in row and unique identifier in column

    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...
  2. M

    Sum Column Values if Matching Values Exist In Another Column, excluding first occurence

    Hi all, Hoping you can help. I'm in need of a formula to search if a cell's value is a duplicate, triplicate, etc within a column. If multiples are found, I want to sum the values from another column in those rows - excluding the first occurence. In my example below, I'm trying to find a...
  3. P

    Summing by Quarter (dates are in header row)

    Hello Experts, How can I sum this data by quarter? Dates are in row 1 as a header. I would like to have a pivot and date filter and sum by quarter but I seem to remember that having dates in a header row creates issues. Beneficiary Issuing Entity All In Rate 4/1/2020 5/1/2020 6/1/2020...
  4. I

    Vlookup with sum and multiple returns

    Hello. I have workbook that lists badge numbers in column A. Hours in column H. I want to be able to add up all the hours by badge id. The problem is the a badge number can appear multiple times. So a regular Vlookup will not work. I have also tried a sumif formula. This adds them up however, it...
  5. A

    Comparing first two weeks to last two weeks

    I have weekly data. I would like to create a monthly indicator showing the % change from the last two weeks compared to the last two weeks for each month. The output should look as follows: A B 1 16M01 % (where this calculation is ((16W03+16W04)-(16W01+16W02)) /...
  6. A

    Most optimal combination

    Hi, I have a set of unique items that I have to assign to specified locations. The number of locations is determined by the sum of the items' values, rounded up to the closest integer. See for an example below: Item Value A 0,11 B 0,22 C 0,44 D 0,44 E 0,67 F 0,33 G 0,25 H 0,5...
  7. D

    SUM all cells to the right

    I have a table which contains Transaction Total in column N, all columns to the right are different transaction types and their cells contain the monthly total. There is the possibility for additional columns for new transaction types to be added to the right of column N. Because of this, I am...
  8. L

    Using INDEX MATCH to sum over a range, now wanting to add in an 'if' in to the formula

    Hi, Table 1 has many rows with costs in & in Table 2 I want to sum up these costs. Originally Table 1 did not have currencies in, so I was able to use the below INDEX MATCH formula to do this: =SUM(INDEX(2:6,N(IF(1,{1,2,3,2,5})),MATCH(C9,1:1,0))). Now I want to split the total of these 5...
  9. K

    How to exclude duplicate rows when summing values in columns

    Hi guys. I opened a thread yesterday about the same problem, but I couldn't fully integrate the solutions people gave me in my own project. The chart below is a part of a course programme that has these elements: 1. In column A are the initials of four teachers. 2. Column B are the subjects...
  10. A

    Find total due according to date

    Dear All, I want a formula which will sum monthly amount in due column in accordance with the current month. What I want is, if the amount is 120 and it should display 120*2="240" in the total due column after 28th february, and when it comes to March 31st it should display 120*3=360 and so...
  11. M

    Sum from previous row, different column

    hi all, I was wondering if I could add from a previous row in a different column. from what I have pasted, for example the debit is add and the credit is subtract. in row 12 the credit(column G) would go into H, then the next row would either be debit or credit, whichever one it is would add or...
  12. M

    sum and 2 tables

    i am creating a document for work to help things flow faster and easier. This is a shipment plan, it is mainly done but its just the last part which im struggling with and hoping someone will have an answer. I am trying to summarise 1 sheet with 2 tables (for printing purpose) on another sheet...
  13. B


    I have a sheet with data in for months, i have done 1 count if statement to count the number of cars sold in 1 month based on the month being filtered on another cell. What i want to do is add another part to the formula to say "Count if the number of cars sold in column X when the month is X...
  14. U

    Give a Range Between Two Specific Texts instead of two Cells

    Hi guys i asked this question in 3 post(SUM in changeable range between two texts) but i still does not solved for me. i want this: Sheet 1 ABCD1NameDateSum 1Sum 22Customer ALast Date Of Customer ASum D:E Start at Customer A and End before Customer B For Example Until Last Row Added (15) =...
  15. B

    Sum of vlookup in multiple rows with condition

    Hi, Let me try and explain what I need with a simplified example: I have a table (H3:I5) that gives me the value for each item (I have dozens of items in the reality). I need, for each month (in B9, C9, D9), to know the sum of the value of each item for the particular month... but only...
  16. U

    SUM in changeable range between two texts

    Hi guys How set a range for sum that changeable and add cells or remove cells in this range... And show the result just in one cell not a specific column for this, how can i do this? (For example instead of =SUM(D1:D30) if in the D1 cell written CUST A and in D30 CUST, just sum between this two...
  17. U

    Sum Until A Colored Cell

    Hi guys i have a sheet that i have some customers and all customers continuous from the previous. for example customer 1 start from A1 that have customer name and colored and with a color and continue to A62 and a row afterthat that colored with a color that the role of the separator between...
  18. J

    Consolidate multiple workbooks with multiple sheets into a Masterfile; maintaining sheet names and Summation of values

    Hi. I have multiple workbooks with multiple sheets. The workbooks each have the same layout and sheet names. What's different would be the values in each tab. I have a master workbook, same number of sheets with sheet names, etc. which I would like to be updated with the *summation* of the...
  19. E

    VBA Recipe calculator sum the ingredients

    Hello, I need a piece of code that will calculate the quantities of needed ingredients for some products but i am stuck with some 2d arrays and the code messed up. Can you please help me? In the attached images, -the result for ingredient1 is 40 because we need 20 cakes and we use 2 pcs in...
  20. W

    SUMIF for all sheets between 2 specified sheets

    I am new to spreadsheets and have no training. My knowledge is very limited. Screenshots of the spreadsheet I’m creating are attached. I am attempting to total all miles and fuel by state on sheet “Quarterly IFTA Report” for all the sheets between the sheets “Start” and “Stop” Additional sheets...

Watch MrExcel Video

This Week's Hot Topics

We've detected that you are using an adblocker.

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
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 "".
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.
Go back