# sumifs

1. ### Avoiding INDIRECT when referencing and comparing ranges in different workbooks

I work in Finance and have a QoL-issue with excelling daily. I use INDIRECT and SUMIFS constantly when comparing figures. Oftentimes it's about comparing latest estimates with previous forecasts or budgets in an earlier instances of the same file (although with a different name)...
2. ### Sumproduct with 3 tables?

Dear users, I am trying to do the following but have been unsuccessful therefore I seek your help; I have vendors who buy TVs from certain Towns. The top left table displays the quantities of TVs bought by each vendor who has a record of purchase while the bottom left table displays the...
3. ### using sumifs function across multiple sheets

i have a sumifs function that i need to to also add cells that match the same criteria from other worksheetsABCDE111-Feb17-Feb234561015021015491036427JeffJustinRamy8hours24.8037.8910111213A1A1=WORKDAY.INTL(B1+1,-1,"1111110")C8C8=SUMIFS('Aggregated Data'!C4:AG4,'Aggregated...
4. ### Pivot Table Sum giving Zeros but count works

I have a workbook that is helping my team track weekly inventory of something in our lab. We scan one barcode that contains the information required for weekly inventory (Lot ID, Variety, and number of pouches). The next three columns after the scan will split the text by a delimiter using the...
5. ### SUMIFS not Working

Iam trying to calculate the sum of QTY mentioned as per week start and week end dates , but for some reason it is not reading the qty if the dates are outside of the range. Please see the highlighted cells as an example. Can you please tell how to fix this. Thank you so much in advance...
6. ### Dividing a sumifs() statement by another sumifs() statement - Problem: Second sumifs() not recognizing valid ranges.

Hi, I am trying to divide the result of one sumifs() statement by the result of another sumifs() statement, but the sum range and criteria ranges referenced in the second statement are not working.The ranges are valid but they are not highlighted in the sheet and the formula returns an error...
7. ### Power Query SUMIFS Equivalent

I am looking for a power query custom column formula that will do the equivalent to this Excel SUMIFS formula below =SUMIFS(Country_Variety_Sales[2023 TOTAL],Country_Variety_Sales[Secondary No],Country_Variety_Sales[@[Secondary No]],Country_Variety_Sales[Country],Country_Variety_Sales[@Country])
8. ### sheetlist sumifs with multiple criteria - shortened?

Is there a way I can shorten this formula. Works - This adds "US", "VI", "PR", and excludes "CA"...
9. ### total (weight) per shift and date calculated in every row of 1st trip

Hello together, I have the following problem. I want the total weight of the tonnage per shift (Column shift - 1 or 2) and by date. The total weight should be added in each row of the 1st trip. As can be seen in the column "My incomplete result", I have succeeded for the 1st shift. The...
10. ### Return Text Based on Number Criterea

This should be really simple, and I just can't get it. I am very familiar with sumifs, but the issue is that my sum range is text and my criteria are numbers. So I always get a zero return. =sumifs(A:A,B:B,C1) Need to return the ttt A B C ttt 1000 1000 rrr 1111 eee 2222
11. ### SPILL error when SUMIFS on pivot table

Hi, I was using a SUMIFS formula to look up an external workbook which worked great. I then decided to pivot table that external workbook so that the data was contained within my file, and users of the file did not need to open that external workbook each time. However, since doing this...
12. ### SUMIFS Statement

I need a formula that is using the data from the table named "Costs". I need to sum the Hours column IF the Concatenate column has any of these values...162FOL, 392FAL, 392FGL, 392FFL, 392PFL, or 452FGL AND the date is before 6/1/2023. This is what I currently have and it is returning a result...
13. ### Count Blank If Multiple Conditions Match

Hello, I am trying to count blanks when multiple values match by using COUNTIFS() or SUMIFS(). For example, I would like to get the sum of blanks in Apr for values A only in column B. However, whenever I try to do it, I either get a #SPILL! error or #VALUE error. Would be grateful for any...
14. ### Dynamic Sumifs Formula: Multiple Row Criteria, Dynamic Column

I have a data table say A1:N100 Column A is Category Column B is Sub Category Columns C:N would be Jan - Dec I need a dynamic sum that only sums up if Category A value is "Criteria1", Category B value is "Criteria2" and for only the month of my choosing. Sometimes this will be current month...
15. ### Complicated SUMIFS/COUNTIFS Formulas Required

Hello, I have two sheets, named: 1. Master Data - This sheet will be updated daily by pasting a system report over the existing data 2. 2022 - I want to calculate some stats from the master data sheet on this sheet The gist of this is, I need to report on people who leave between 0 and 5...
16. ### Calculate absences within a period

Hello I'm hoping you can help if possible please. I have a spreadsheet with 3 tabs, on tab 1 is the formulas / calculations, tab 2 holds all the data and tab 3 has dates (start/end date). I am trying to calculate how many occasions of absence an employee has within a 6 month period and...
17. ### If Cell is Blank then do no generate a Sumif for the Month

Hello All, This is probably an easy ask, but i'm struggle to find my solution. I'm trying to generate a blank cell if another cell has zero data else i want to run the sumifs formula. Hopefully this is easy to understand. Below is the example: Jan Feb Mar Actuals has sumif formula has...
18. ### Calculating numbers only with Sumifs

Hi all, I hope you can help. I am using a SUMIFS formula to count totals in column D - however the will be times when a letter will be listed instead of a number, which obviously causes the formula to fail. I have used connotations of ISNUMBER within the formula, but none of these work. Any...
19. ### SumIFs based on multiple conditions and multiply by a value if certain text present

Hi All, I am hoping you could help on this one. I need my formula to search 2 columns in excel. One column is based on the asset type. The other is based on the currency. I need my formula to return the total by asset type and in SGD equivalent currency. From my larger data set, i am...
20. ### SUMIFS(AND

Hi everyone, I have a question with two columns: if a column of mine holds a value such as "100 012", "800 002", "500 012", or "800 004", and my second column or updated value holds a value such as "800 007", "800 008", "800 003" or "97", I want the balance of that specific row. Realistically I...

### 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 MrExcel.com.

### Which adblocker are you using?

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

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