sumifs array

  1. R

    Sumifs with posetive criteria array and negative criteria array

    First of all sorry for my English Hello , i have problem with sumifs formula, I have 3 condition and one of them is negative, i tried this formula but the answers is just effect one of my negative cell , i have the example table for my problem in the attachment , thanks...
  2. S

    My brain can't comprehend the logic - sumifs, index, match, sumproduct, multiple row, multiple column

    Hello there - I keep forgetting how to think about the logic of these functions. I tried to look across my other workbooks for formulas. I feel like I've done this before so I apologize but I couldn't find the formula hence why I"m posting. I've searched and using these links and posts as...
  3. S

    SUMIFS - SUMPRODUCT - List of ranges as criteria

    How to SUM values in a table, with criteria based on three columns, in each one column the criteria are many values on vertical ranges? SUMIFS works, but for only one range as criteria, the other criteria range must be a single value.
  4. I

    Array in If statement

    I have above sample data. In cells F9 and F10 I want to sum 'Sales' (C5:C10) for the respective months (Jan, Feb) based on the user input in cell F3. With following formula I can get the sum of sales for an individual product per month: =SUMIFS(C5:C10,B5:B10,E9,A5:A10,F3). To get the sum of...
  5. T

    SUMIFS combined with summing values with same value

    To be honest I don't know if the headline is correct but it was the best I could come up with. I have a simple sumifs formula summing L when M="Active" and P=Projects!B114: =SUMIFS(Quotes!L:L;Quotes!M:M;"Active";Quotes!P:P;Projects!B114) I want to extend the formula also to look at column...
  6. P

    Multiple array SUMIFS (Tryed SUMIFS with INDEX and MATCH)

    Hi I need your help with this: I need to sum by multiple criteria located on rows and columns. the problem is in picture attachement I tryed SUMIFS with INDEX and MATCH - but it gives me only first occurance I tryed SUMPRODUCT - but it works for less criteria Can you please help, me with...
  7. A

    Sumifs

    Hi All, I have range of cells B21 to N22. I cell B21 is a value of 8 and B22 a value of 9, C21 a value of 7 and C22 a value of 5. What I'd like is to have is a formula that will sum up the below. I have just used nested If statements ( I know this is not what I'm after, but more so, to give...
  8. L

    SUMIFS/INDEX-MATCH based on criteria in 2 rows and 1 column

    I'm using Google Sheets. In another tab, I'm trying to match in the values (in the white part of the table) based on the three criteria. So for example, I'm wanting to match in the 0.2856... for criteria: Member #1, 1, A.
  9. L

    SUMIFS with 2 row and 1 column Google sheets

    I'm trying to match in a value (either using SUMIFS or INDEX MATCH) based on multiple criteria (two rows, one column).
  10. R

    Sumifs, Lists, and Sumproduct to create a matrix?

    Hello, I am trying to create a matrix that includes specific data from different Excel workbooks. However, since I don't know how to use macros, I'm going to transfer data from each workbook to the appropriate day column in my matrix, one by one. I will use trains carrying food products as my...
  11. A

    sumifs only first occurrence based on two criteria

    hi all, I have data that's formatted as follows: <colgroup><col><col><col></colgroup><tbody> Col_A Ontario Col_B Ontario1 Col_C 1 Ontario Ontario1 1 Ontario Ontario2 2 Ontario Ontario2 2 Ontario Ontario2 2 Alberta Alberta1 3 Alberta Alberta1 3 Alberta Alberta1 3 Alberta...
  12. R

    SUM/SUMIFS with Conditional Array & Wildcards

    Hello! I'm working with the following section of a formula: SUM(SUMIFS('All Facilities'!$AC:$AC,'All Facilities'!$F:$F,$C$29,'All Facilities'!$AR:$AR,{"Operating","2017*"})) It's working just fine (and yes, entered with ctrl+shift+enter), but I want to make an adjustment to it: That last part...
  13. M

    Using SUMIFS for Both Categories and Between Date Ranges for Different Array Sizes

    I currently have transactions separated by both category and month. I am looking to calculate the sum for a particular category between date ranges. Since the "sum_range" input is different sized than the "criteria_array" I was wondering if there exists a workaround to solve this. For an example...
  14. E

    Multiple tabulation

    Howdy...Looking for tips to achieve multiple sums. I have a list of criteria along with scores assigned to each criterion, which will be used in drop-down lists in another sheet. <colgroup><col><col><col span="7"></colgroup><tbody> <tbody> Txt 1 for criterion 1 Text 2 for criterion 1...
  15. V

    SumIf Formula with multiple criterias in one column

    How can I do a sumifs field with multiple criterias in a column? Example: - the values are in row c - the first lookup column is in row b. The lookup field is months. - the second lookup column is in row a. The lookup field is category type (various expense type) Desired Output: I want...
  16. M

    Generate a cost from a pricing matrix with various options

    Hi all, I am trying to create a formula that will allow a user to simply fill in some criteria and a price will generate accordingly. I have tried to use a SUMIFS formula but have only got as far as doing the basic price without any of the optional extras. Here is an example of the...
  17. G

    Converting SUMIFS to array formula

    I have two formulas linked to other workbooks, but I'm getting the #VALUE error when they are closed (they work fine when everything is open). I've tried to figure out how to do this, but I don't have a ton of experience with array formulas and I'm having trouble with it. I'm hoping someone can...
  18. P

    SUM data from a column if value from another column follows a specific value

    Hi, I'm trying devise a formula to sum the data from the "Seconds" column whenever a "1" follows a "4" from the "Mode" column. I don't want to sum where there are "4's" or "1's" following other "1's". Here's an example (in this case the sum would be 59): <tbody> Seconds Mode 2 0 6 4 4...
  19. S

    Using SUMIFS with both vertical and horizontal arrays

    Hello, I am trying to figure out a way to return one value based on two criteria. One criteria is in a vertical array, whereas the other is in a horizontal array. See the tables below for more details. Table 1 <tbody> A1 text text text 10.01 text text FORMULA 10.02 </tbody> Table...
  20. D

    SUMIFS - using the year function as part of an array

    I'm trying to us both sumifs and countifs to gather various information on a set of loans. I'm wanting to look at the year the loan was originated as one of my criteria in these statements, but the dates are stored with the actual date. I find that sumifs and countifs are generally MUCH...

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