# Use Sumifs Function Applying 2 Criteria?

Aug 21, 2013

Excel.xlsx

Attached is the example of the data, I am trying to sum the amount applying 2 criteria, within a particular month and other category. The formula tried is

=SUMIFS(D5:D10,C5:C10,"*June",E5:E10,H1)

However it is giving a value of zero only. I have tried other combinations as well.

## SUMIFS Function With OR Statement In One Criteria?

Apr 20, 2014

I need to do a SUMIFS with 2 sets of Criteria, but I need the second Criteria to be an OR Statement. The numbers I am adding up is in Column E, with any where from about 20,000 to about 60,000 records (will fluctuate all the time). The first Criteria is easy - match up the name in AG12 with any of the names in Column C. The second criteria is to match up the numbers in Column A with any of the numbers in AD1 through AD40.

I tried this but it didn't work: =SUMIFS(E:E,C:C,AG12,A:A,AD1:AD40). I've alos tried a million other variations of that, imbedded SUM(IF( statements, SUMPRODUCT statements, and DSUM statements and no luck. I can't find anything that allows me to make that second second criteria look at the numbers in Column AD as being AD1 or AD2 or AD3 so on and so forth.

## Applying A Mid Function To Many Rows

Jun 14, 2007

I need to cut off addresses by 30 characters. I am doing this using the MID function. However, being a newb, how can I apply this function to say D4, D5 all the way to D899, without copy and pasting for 800 times? I did search this first, and couldn't find a resolution.

## Applying Function To Certain Cells Only Based On Another Column

Aug 17, 2014

Supposing you would like to find the median of numbers in column D, but only for rows that have the word "Jones" in column A? Is there a way to do this? Obviously I know I could do a sort and simply specify the range myself, but we're dealing with nearly 2000 rows and a LOT of different values in column A

It occurs to me that it would be even better if I could ask excel, in effect, to identify every separate text string in column a and then find the median of the corresponding numbers in column D. In other words I wouldn't have to type any strings from column A at all.

## Applying Function Arguments As A Control/class Name

Mar 16, 2007

I'm not sure what verbage to use in order to describe what I'm trying to accomplish.

Let me give you a piece of code and the question will be clear.

End Function

I would like to use this function later in the code in the form of:

VBA will error out with "Run-time error '424': Object required". I know I need to declare the variable arg1 as a type in order to use it in this form, but I don't know the correct data type.

I also ran across a similar issue like this where the advice was to use the Set function in the form of:

End Function

## SUMIFS With Two Or More Criteria?

Aug 11, 2012

I have the following formula in Col C

=SUMIFS(C\$275:C\$379,\$A275:\$A\$379,{"Idle Time","Oil stock *"})

The criteria "Idle Time" & "Oil stock losses" are in Col A.

I would like to add the values in Col C based on "Idle Time" & "Oil stock losses" which are in Col A

The formula =SUMIFS(C\$275:C\$379,\$A275:\$A\$379,{"Idle Time","Oil stock *"}) adds the idle time value correctly, but ignore the values of Oil Stock losses

## Extracting Consecutive Letters From Cell And Applying IF Function

Mar 8, 2013

I'm working on an email database and am looking to sort them into type. I am trying (without success) to use the IF function as follows.

If A1 contains the letters "isd" consecutively, then B1=TRUE

If A1 contains the letters "google", "yahoo", "hotmail", or "austin.rr" consecutively, then B2=true

## How To Use COUNTIFS Function Only For Visible Data After Applying Filter

May 27, 2014

How to use COUNTIFS Function only for the visible Data after applying filter.

Ex:=COUNTIFS(A:A,"Ret",P:P,"M")-COUNTIFS(A:A,"Ret",P:P,"M",B:B,"") it gives d result including hidden data, but i want it only for visible data after applying filter.

## SUMIFS With Criteria Range

Oct 31, 2013

Having issues trying to do a sumifs function using multiple criteria to get a final total. I want to sum a list of values that have different transaction types from a transaction table. The only working way I have found is to do a sumif and create a CSE formula for it and drag it down and sum to the total. I am looking for a way to sum the the entire list of transaction types I need from the transaction tab and get to the total value rather than have to use a CSE and then sum. Basically I want to take out the sum of the CSE results....Is there anyway to do this?

## SUMIFS With Multiple Criteria

Apr 11, 2014

Here is a sample of the formula I am currently using.

Formula:

[Code]....

This works perfectly for what I was needing but I am adding more criteria and running into a problem. I am rolling together usage for parts with different part numbers but the same function in order to reduce SKUs (as well as capture replaced SKUs) needed. These are located in Columns A:G. what i would like to add is more conditions to where the parts are going. Currently I have one location located in cell B2. To analize my usage further, I want to add another location in C2. So i want my formula to look like this.

Formula:

[Code] .....

when I use this it still only adds up with what matches in B2 regardless of the value in C2. Conversely, I can get the formula to work if I take out the extra part numbers.

Formula:

[Code] ....

## SUMIFS More Than One Criteria And A Month

May 24, 2014

Attached is a spreadsheet. The first worksheet is a summary of the data inputted in the second. I am trying to total the "total spend field depending on whether it is "Extra Works", "Spares & Materials" or "Other" which works fine. I would now like to sum depending also on the month that it was spent.

Order Tracker 2.xlsx‎

## SUMIFS - Cannot Use The Same Criteria Range?

Dec 23, 2013

Following the syntax of the SUMIFS formula --> Can "criteria_range1" be the same as "criteria_range2" ?

It didn't work for me and I got the result as 0.

## SUMIFS With Criteria Range?

Dec 19, 2012

I have the following formula that I did:

=SUMIFS(Sheet2!L:L, Sheet2!B:B, 1, Sheet3!A:A, Sheet3!A5, Sheet2!X:X, "LOL", Sheet2!R:R, "delivered")+SUMIFS(Sheet2!L:L, Sheet2!B:B, 1, Sheet3!A:A, Sheet3!A5, Sheet2!X:X, "LOL", Sheet2!R:R, "shipped")

As you can see I have exactly same thing in the first SUMIFS function, and the second one except for the "delivered" and "shipped" criteria. Now, what I would like to do is to create a range that would be used instead of a single criteria.

Actually, I have more than 20 criteria but I don't want to do 20 separate SUMIFS and then adding them. Best would be if I could select a range of criterias (I would like to be updating that list easily in the future).

## SUMIFS Using Arrays As Criteria?

Feb 28, 2014

The below formula is working fine:

=SUM(SUMIFS(YTD_GBP,Region,"Central Budgets",Cost_Desc,"Hardware",Non_Billable_Project,{"Non Billable","Central Budget"}))/1000

but if I try to add in a second criteria for Region, as below:

=SUM(SUMIFS(YTD_GBP,Region,{"Central Budgets","Corporate Services"},Cost_Desc,"Hardware",Non_Billable_Project,{"Non Billable","Central Budget"}))/1000

I get zeros.

But if I do two separate formulas and add them:

=(SUM(SUMIFS(YTD_GBP,Region,"Central Budgets",Cost_Desc,"Hardware",Non_Billable_Project,{"Non Billable","Central Budget"}))+SUM(SUMIFS(YTD_GBP,Region,"Corporate Services",Cost_Desc,"Hardware",Non_Billable_Project,{"Non Billable","Central Budget"})))/1000

## Use Forumula As Criteria For Sumifs?

Nov 22, 2008

I'm trying to sort out an accounts receivable ageing into various age categories For example, for a customer with 3 months credit term, i've used the formula =SUMIFS(\$C3:\$AA3,\$C\$1:\$AA\$1,">=4",\$C\$1:\$AA\$1,"<=9") to sum up amounts that are 1 to 6 months past due. For the next age category 7 to 12 months past due i've used =SUMIFS(\$C3:\$AA3,\$C\$1:\$AA\$1,">=10",\$C\$1:\$AA\$1,"<=15").

As my customers have different credit terms (30 days, 60 days etc), i've to adjust the above formula accordingly e.g. instead of ">=4", I'll have to change the criterion to ">=7". This is time consuming as i've got a long customer list.

## Sumifs With Multiple Criteria?

Jul 12, 2012

I created the following formula and cannot get it the correct result. The return result is only adding column "P' with "275". How can I get it to recognize "276,"278"?

=SUM(SUMIFS('Supplies Only'!N:N,'Supplies Only'!E:E,{"2","26","27","59","86"},'Supplies Only'!P:P,{"275","276","278"}))

## SUMIFS With Alphanumeric Criteria

Jul 7, 2014

I am trying to create a SUMIFS statement with an alphanumeric criterion. I have found formulas that delete specific alpha-strings from the alphanumeric criterion before summing, but I have multiple different alpha-strings for the different departments.

Right now I have:

Code:
=SUMIFS(E4:E100,G4:G100,”>1.67”,)

But I need to add in that the alphanumeric values in C4:C100 are >= 3000. There are always 3 letters at the beginning of the course codes and then a space before the 4-digit number.

I tried

Code:
=SUMIFS(E4:E100,G4:G100, ">1.67",C4:C100,">=*3000")

But that, alas, did not work and it does not appear RIGHT works with SUMIFS.

## Multiple Criteria Parameters In SUMIFS

Apr 29, 2014

I have a below table as below

A B C
a aa 1
a aa 1
a dd 1
a aa 1
b aa 1
b bb 1
b aa 1
b bb 1
c cc 1
c bb 1
c bb 1
c cc 1
d cc 1
d aa 1
d bb 1
d cc 1

When i put the formula

=SUMPRODUCT(SUMIFS(C1:C16,A1:A16,{"a","b","c"}))

it returns 12 However when i put

=SUMPRODUCT(SUMIFS(C1:C16,A1:A16,{"a","b","c"},B1:B16,{"aa","bb"}))

it returns only 5

I don't want to use multi formula like

=SUMPRODUCT(SUMIFS(C1:C16,A1:A16,{"a","b","c"},B1:B16,"aa"})) + SUMPRODUCT(SUMIFS(C1:C16,A1:A16,{"a","b","c"},B1:B16,"bb"}))

I want to achieve this using SUMIFS only

## Sumifs / Multiple Criteria / One Of Them Being A List?

Jan 16, 2014

I will take out the different sheet references and instead show it as if it all on the same sheet.

=SUMIFS(F7:F1000,D7:D1000,C37,I7:I1000,P2:P5))

Im trying to get this too work.

So F7:F1000, is the data to be added together

D7:D1000 = the year

C37 = 2014

I7:I1000 = category, so this has stuff like (food shopping, fuel, car, bills, utilities etc)

P2:P5 = three criterias ([Balance], [Cashback], [Transfer])

Im trying to sum together the values which is 2014, and has the text [Balance] or [Cashback] or [Transfer] in the category column.

## How To Use Sumifs To Meet 2 Criteria And Then Sum One Column

Mar 13, 2014

How can u use sumifs to meet 2 critera and then sum the one column..

=SUMIFS('[South Pac Load Tracker - MAR14.xlsm]SPECIALS TEAM NZ'!\$M\$8:\$M\$100000,'[South Pac Load Tracker - MAR14.xlsm]SPECIALS TEAM NZ'!\$C\$8:\$C\$100000,B5,'[South Pac Load Tracker - MAR14.xlsm]SPECIALS TEAM NZ'!\$B8:\$B100000,\$A\$2)

That is the formula i used and it worked, yet when i try to use it for other names (where b5 would be) the numbers skew and do whatever they want..

I just want to sum all of the values in M:M which have B5 or "fiona" in columns C:C and also match the date from B:B with A2.

I believe ive done it right and as i say it does work with the first name in b5 but when i pull the formula down it doesnt work anymore...

## SUMIFS With Multiple Criteria Between Sheets?

Mar 21, 2014

I'm trying to sum data based on a the criteria of a date being between a certain range and sorted by a particular name.

I have included a sample sheet with two tabs:

Totals-I would like to get the sum of the hours a person worked in the range of dates being 1/1/2014-1/24/2014 (which is D1 and E1 in the current charges sheet) Current Charges-this is the page that I would like to gather the information from.

Below is a sample of the formula I was trying to use, but I get an error....my interpretation of what I was trying to input is as follows: Sum C2:C34 in the current charge sheet if B2:B34 is greater than or equal to D1 on the current charges tab (which is 1/1/2014) and B2:B34 is less than or equal to E1 (which is 1/24/2014) with A4:A34 in the current charges sheet equal to A2 on Totals sheet.

SUMIFS('Current Charges'!C2:C34,'Current Charges'!\$B\$2:\$B\$34,">="&'Current Charges'!\$D\$1,Current Charges'!\$B\$2:\$B\$34,"<="&'Current Charges'!\$E\$1,'Current Charges'!\$A\$4:\$A\$34,A2)

Test Data:

SUMIFS Test Data.xlsx

## Using Absolute Value For Criteria Range In SumIfs

Jul 8, 2014

So I have a formula as follows:

Although it is not working because I cannot use ABS() on a criteria range. is there any other way that I can have this formula sum the values if the absolute value of the number in that row is equal to the absolute value of 1 or more values in the same column but a different row?

## Lookup Worksheets & Do Sumifs Criteria

May 28, 2009

Could ANYONE help me solve this formula based on critera sheet name.

On my main sheet workbook i have all the sheet names in column A with their description in column B. In column C i would like to have sort of this formula with result as total.
eg....

## Order Of Criteria In SUMIFS Matters?

Oct 3, 2013

sumifs I was using but eventually got it to work by switching the order of the criteria. how the order of the criteria affects the result?

Working formula:
=SUMIFS('QB data 2011-2013'!\$F:\$F,'QB data 2011-2013'!\$L:\$L,'Daily Report Summary'!\$M\$4-1,'QB data 2011-2013'!\$M:\$M,'Daily Report Summary'!\$N\$4,'QB data 2011-2013'!\$C:\$C,'Daily Report Summary'!\$J10,'QB data 2011-2013'!\$H:\$H,4)

Failing formula:
=SUMIFS('QB data 2011-2013'!\$F:\$F,'QB data 2011-2013'!\$H:\$H,4,'QB data 2011-2013'!\$L:\$L,'Daily Report Summary'!\$M\$4-1,'QB data 2011-2013'!\$M:\$M,'Daily Report Summary'!\$N\$4,'QB data 2011-2013'!\$C:\$C,'Daily Report Summary'!\$J10)

The only difference between the two is that the criteria referencing "4" is at the end of the working formula VS the beginning of the failing formula.

I have never experienced the order having any affect on the returned value, but in this case the top formula works but the bottom returns 0.

## SUMIFS With Date Range As Criteria?

Dec 9, 2013

I have time-worked spreadsheet use to calculate the hours worked over a year, now I need to sum the hours worked during a given period if they happen to be on a public holiday. I have a column (say A) displaying dates from 1/1/13 till 31/12/13, another column giving the result of the worked hours (say B). I also have a defined list of public holidays which I need to compare with (named Holidays). The working year is "divided" in 13 periods of 28 days each (the last one having 29 days). I was trying to use the SUMIFS function (since i have other constrains that need to be met)
e.g. =SUMIFS(B1:B28,A1:A28,Holidays)

## SumIfs Statement And Range Of Criteria?

Mar 19, 2014

I'm trying to put together a SumIfs formula to find the total count of procedure codes during a certain time period, for a given market.

I have the Market column, Procedure Code column, Date column, and count of Procedure Codes column.

The range of Procedure Codes I want to total on are in cells C4:F4 and they are:

99307993089930999310

It's fairly straightforward with the dates in the SumIfs as well as markets, but I'm not able to get the range of Procedure codes to work like I want. These 4 codes are just the criteria that will use to pull the totals from, which comes from a much larger dataset.

## SUMIFS With Text And Date Criteria?

May 3, 2014

Let's say I have a simple table like below:

Region
Date
Qty

N. America
23/4/2014
10

EMEA
24/4/2014
50

EMEA
25/5/2014
20

I want to sum up all EMEA quantities that are in the month of April.

I've tried this:

SUMIFS(C:C,A:A,"=EMEA",MONTH(B:B),"=4")

I've also tried:
{=SUMPRODUCT(IF(A:A="EMEA",1,0)*IF(MONTH(B:B)=4,1,0)*C:C)}

Both do not work.

Also as the data grows each month, I would want to refer to the entire column in my formula, e.g. 'Region' as A:A (rather than A2:A4).

## Use The Sumifs Function To Add A Column?

Jan 7, 2014

I am trying to use the sumifs function to add a column that is in pounds but with multiple criteria.I am trying to add a price field but between two dates (between the beginning and end of each month) another criteria from a range of cells I have copied the formula I have written below

=SUMIFS(Sheet1!K:K,Sheet1!B:B,">="&I17,Sheet1!B:B,"<="&J17,Sheet1!W:W,"="&M17)

try to explain a little column K is the column I need to sum column B is the date field that I need to extract i.e. 1st to 31st January (I have put these dates in a cell that correspond to I17 and J17)

then the last bit column W is another criteria that I need met, this works for just one cell however if I try to add another criteria in another cell it doesn't work after M17 I add to the formula ,Sheet1!W:W,"="&M18)

## Sumifs Date Function

Nov 20, 2009

Is there an easier way to construct the formula in the sales revenue col of the monthly analysis table on the summary tab so that one does not have to enter the specific dates into the formula?!

## SUMIF OR SUMIFS Functions With Criteria As Formula?

Apr 28, 2014

SUMIF and SUMIFS formula, where in I want to set criteria in the formula as greater than or less than or equal to value derived from another formula. I am inserting the following formula but excel is not allowing me to enter the formula.

=SUMIFS(\$C\$22:\$C\$30,\$E\$22:\$E\$30,>=LEFT(L21,3),\$E\$22:\$E\$30,<=RIGHT(L21,3),\$A\$22:\$A\$30,G22)