1. A

    Need Help on Sumif using rank based on criteria (which changes)

    Hi Team, This is my first post and apologies if I am overstepping any rules here. I have gone through similar posts, but didnt find a way forward. There are similar threads, but the issue here is a bit different. The problem Statement is as follows - I need to calculate the time taken before a...
  2. A

    Sorted array of unique items and their values with filter and sumifs

    I am currently working on an excel formula which give me a sorted array of unique items and their values. I have reconciled the steps in the following excel but couldnt crack it to one step. The difficulty is also higher due to the multiplication of two colomns (i.e. asset value & % ownership)...
  3. I

    Sumifs vs Sumproduct on partially blank cell

    Hey All, I have the below table, and instead of having "B", I have formula that goes : If(xxx,"Text", ""), and as it stands B should then reflect: "". When I use the formula: sumifs(range Y,Range Y, "<>"), the formula reverts 3; but if I use sumproduct ((range X <> "")*(Range Y)) the formula...
  4. B

    VBA code to sumifs and split across Business Case and Managers

    Hi Can someone help me with below requirement. I have base data in sheet1 with Business Case ID's and Managers etc as below. X to AI columns will have Jan to Dec data. I am only allowed to present some sample data due to confidentiality. X Y AI AJ AM AN 1-Jan 2-Feb 12-Dec Yes/No Manager...
  5. M

    Replacing OFFSET to sum up to max value from specific cell

    Hello everyone, I am new to the board & thank you all in advance for the help. I have data for the time it takes each step in a manufacturing process & each step has a unique name that never repeats, as seen below, pasted in A1 corner. Steps time Start Point Sum of Steps Count 2800N1 5...
  6. B

    Sum(Sumifs or sumproduct(sumifs?

    Hi! I've used the formula =SUMPRODUCT(SUMIFS(E19:E27;A19:A27;{"Team 1";"Team 4";"Team 8"})) to calculate the forecast for specific teams but I want to use the cell reference (A20;A22;A26) instead of the actual team name. Am I using the wrong formula for that?
  7. T

    Sumproduct with multiple row and column criteria

    I have been trying to solve this for about a week and cannot seem to figured this out. I have several sections of a worksheet that is for 10 types of work being performed, the status of the work, and an estimate of the cost of that work. It looks like this, but has a lot of other items in...
  8. H

    Help with COUNTIFS

    Hi, I am trying to COUNTIFS function with a few different conditions: =countifs('Key Property Info'!A:A,"media",'Key Property Info'!H:H,{"contract sent","apps sent"}) I would like excel to return the value of the the contract sent and apps sent figures, where they are applicable to 'media'...
  9. K

    Sumif all columns up till meeting the criteria

    I have the following data table: Jan Feb Mar Apr May Jun Jul Aug Sep Apple 2 3 1 5 6 3 8 1 4 Pear 5 2 7 0 8 1 2 6 7 Banana 3 6 1 5 3 9 7 8 0 Current month is June for example, then I would like to sum up for Banana line, all numbers up till Jun. That is 3+6+1+5+3+9 = 27. Is there...
  10. ibmy

    Macro of SUMIFS Date & Time cell reference for large data

    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 "...
  11. J

    I there a SUMIFS equivalent in Power Query?

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

    Doing SUMIFS in Power BI

    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...
  13. J

    Need Zeros and Blanks as a Criteria in SUMIFS

    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...
  14. K

    SUMIFS to reference job code/hours in seperate table

    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...
  15. E

    Sumifs with index match match inquiry (could be Sumproduct related)

    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...
  16. B

    Sumifs YTD sales based on year

    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...
  17. SanjayGulatiMusafir

    Refer to the corresponding Cell in another column in SUMIFS

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

    Using SUMIFS across monthly worksheets

    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...
  19. K

    SUMIFS or SUMPRODUCT or something else :(

    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...
  20. T

    SUMIFS criteria based on a manual table

    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)...
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

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