1. C

    Formula returns error and don't know why

    Hi The below formula return #VALUE and i'm not sure why =SUMIFS(INDIRECT("'"&$B$1&"'!$b4:$n17"),INDIRECT("'"&$B$1&"'!$a4:$A17"),$A7,INDIRECT("'"&$B$1&"'!$b2:$n2"),">="&$B2,INDIRECT("'"&$B$1&"'!$b2:$n2"),"<="&$B3) I have tried the below alternative and get #REF...
  2. T

    SUMIFS-like Formula to grab text AND numbers

    Hello - I need a formula that will work in a matrix - I have been using SUMIFS to populate my worksheet. I need a non-array formula (too slow) that can handle multiple criteria that can pull both text and numbers. One criterion in the SUMIFS is a column and the corresponding array are rows...
  3. X

    Sumifs within an Array?

    Is it possible to do a sumifs within an array? I have a large data set (circa 20,000 rows) that I am working on and one of the elements is a sumifs. Currently I'm creating most of the array, pasting it to a sheet, and then updating the remainder of the array by running the sumifs on the sheet...
  4. M


    I have 2 columns on Sheet 2 In column A it says Mon all the way down to A10 Then Tues for a few more cells below, then Weds for a few cells and so on. The list is sorted in order To the right in Column B some cells have numbers in them and some are blank On Sheet 1 I want a summary, so it...
  5. J


    I am attempting to complete SUMIFS and COUNTIFS and am not getting any results. A sample formula I am using is: =COUNTIFS('Tracking Sheet'!$H:$H,"2016",'Tracking Sheet'!$D:$D,"Repaid",'Tracking Sheet'!$D:$D,"Closed") If I break the formula apart and only have two arguments, I get a result. I...
  6. T

    SumIfs' formula with Multiple criteria including dates

    Hello, Hopefully somebody can help me, I am trying to creating a sumifS formula or something I am looking to sum Qtys by date and item number from one sheet and match up to the item on a separate sheet: Sheet1 looks the following: A | B| C | D |...
  7. C

    sum column of numbers based on id # and other possible criteria

    I am trying to do the following but cannot seem to wrap my head around it. I have a workbook with multiple sheets. For what I am doing I have to pull information from one and compare it to another. It looks something like this: Sheet 1 <tbody> A B C D E F 1 Game # Cards Home ID Home...
  8. J

    Sumifs / sumproduct that contains multiple columns

    Hi excel experts! I'm trying to use sumifs that contains multiple columns...So I want to get the value highlighted in red if Color1, Color2, and Color3 contains "red", based on its assigned values. I was trying SUMIFS and SUMPRODUCT, but couldn't really figure it out. <tbody> code (columnA)...
  9. C

    SUMIFS Frustration

    G'day Excel Gods, I normally have no issue with SUMIFS, but I have one spreadsheet that is kicking my butt and driving me insane! I want the total from Column E based on the value in Column B, however I need to exclude the value in Column E if Column C is blank. The formula I have is...
  10. U

    Sum If and Circular Reference

    I am looking for a "less manual" way to do my current process. I have a sales channel "NHOM" or "NHAF" and multiple material groups "W01 or "W02" for example. I pick out the main material groups and then any others are grouped as "Other". There are different types and count of material groups...
  11. H

    VBA Sumifs

    Hi friends, How to write this formula in VBA.... I only want the result not the formula in range H11 to H403. Regards, Humayun
  12. P

    SUMIFS - Greater than first of month, Less than end of month

    Good morning, I have a sheet (Payments) with the following layout: <tbody> Date Account Amount Rec 26/10/19 Barclays 100 Yes 02/11/19 Barclays 100 Yes </tbody> On a different tab (October 19), I am trying to use a SUMIFS formula to gather all payments that occurred to Barclays in October...
  13. G

    SUMIFS between two different workbooks (SUMPRODUCT solution needed)

    Hi, I have recently discovered that SUMIFS only works between two different workbooks if both workbooks are open at the same time. I've also read to get round this, SUMPRODUCT can be used. That said I can't get it working. The SUMIFS version of the formula I need converting to SUMPRODUCT is...
  14. J

    SUMIFS multiple criteria

    Hi, I have two SUMIF formulas columns G, and F in sheet "Order details" are numbers and so is value in D2 but Column F in "Orders" contains dates. The following formulas work by themselves but I need to combine them. =SUMIF('Order Details'!F:F,D2,'Order Details'!G:G)...
  15. T

    SUMIFS and Vlookup

    I cant seem to get my head around SUMIFS and VLOOKUP, Im currently using: =SUMIF('Gas July'!$B$2:$D$217, VLOOKUP($A3, 'Gas July'!$B$2:$D$217, 1, FALSE), 'Gas July'!$D$2:$D$217) Which points to a sheet with only July Data and multiple sites and it gives me the number I want (total usage for...
  16. B

    SUMIFS across multiple columns.

    I am looking to count the values of a certain column, based on various criteria across other columns. What I am currently using is: =(sumifs('Workbook A'!$J:$J,'Workbook A'!$D:$D,$A3,'Workbook A'!$B:$B,">="&CP$1,'Workbook A'!$B:$B,"<="&CP$2)+sumifs('Workbook A'!$J:$J,'Workbook...
  17. D

    Counting instances in workbook with both AND and OR statements

    All, I have a workbook that contains site names, technology at each site, then multiple columns where my KT teams can enter times for conducting knowledge transfer. Column A - Site Name; typically have 4-6 rows containing the Site name Column B - Technology; in rows for each site, there are...
  18. A

    Sumifs using two date ranges

    Hi, I am struggling to find a formula to sum utilisation days using two date ranges which is a dump from a system, ideally i would get users to enter info one day at a time but that's not possible and they group info as per example below: Employee........Date From.......Date To...
  19. N

    Sum of multple values, dependant on specifc date (within broader dates)

    I need to calculate the sum of specific cells, only when the fall within a specific date range. Our data can carry across months- eg Aug 1- Sep 15, however we need to count within a speicfic month only (eg September). I have tried a variety of SUMIFS, but I can't find the magic combination to...
  20. Y

    SUMIFS with two wildcards

    Hi everyone, I am trying to use a version of the SUMIF function to convert 'one-way' flight segments to 'round trip' segments (e.g. sum all flights trips Atlanta - Nashville, and Nashville - Atlanta into one row 'Atlanta - Nashville'. The formulas I come up are quite extensive and long...

Some videos you may like

This Week's Hot Topics