# Nested SUMIF Statement Or Multiple SUMIF

Sep 17, 2009

I need to perform 2 SUMIF's on 2 columns of data to return a result and I'm not quite sure the best way of doing this. I'll give an example below.

I have 2 columns of data, both numeric and the SUMIF needs to say if H1:H100="10" and also if J1:J100="907". I can perform one or the other but not both.

## SUMIF Statement With Multiple Criteria In Same Column

Jan 26, 2004

I'm trying to create a SUMIF statement that that has an embedded OR statement within, and am unable to make it work.

Basically I want to say if "column Q" equals one of 2 criteria ("cat" or "dog") then sum the corresponding number in "column P" I've tried the following statement, but it is just resulting in a zero:

=SUMIF(Q9:Q32792,OR("cat","dog"),P9:P32792)

I've seen support on statements with multiple criteria in DIFFERENT columns, but not if searching for multiple criteria in the SAME column.

## Nested SUMIF

Sep 16, 2008

I've tried various ways of nesting the SUMIF function within itself but just get error messages. Is a nested SUMIF possible? What's the format?

## Consolidating Lists And Nested SumIf(AND / Countif(AND Statements

Jun 10, 2014

I am trying to produce a report of supplier transactions sorted by area code. I have a spreadsheet of data consisting of Supplier Code, Supplier Name, Transaction Amount and Area Code.

I want to be able to firstly seperate the transactions by area, then also consolidate the data so it shows one row per supplier with total amount spent on that supplier and a transaction count on that supplier. I know how to sumif the transaction total and countif the transaction count.

However I have problems consolidating the suppliers in to one row per unique supplier and I also have problems nesting an AND statement in to the sumif/countif statements. I need additional criteria in the forumula to only count transactions in a specified area code.

I've attached an example spreadsheet to make it alot clearer. See results sheet in this workbook.

## Nested Formula (Sumif / Countif) - Sorting Table

Mar 6, 2013

It's actually a sumif/countif that works fine if i sort my table, which i don't want to do.

Here's the original formula:

Code:
(SUMIF(H4:H35,"1",G4:G35)/COUNTIF(H4:H35,"1")*1+SUMIF(H4:H35,"2",G4:G35)
/COUNTIF(H4:H35,"2")*2+SUMIF(H4:H35,"3",G4:G35)
/COUNTIF(H4:H35,"3")*3+SUMIF(H4:H35,"4",G4:G35)
/COUNTIF(H4:H35,"4")*4)/10

I'm trying to apply the formula using some other filters, like if(C4:C35=O5 & I4:I35=M5).

I tried creating a SUMPRODUCT out of it but it was just as bad:

Code:
=(SUMPRODUCT((C4:C35=\$O\$5)*(I4:I35=\$M\$5)*(H4:H35=1)*(G4:G35))
/SUMPRODUCT((C4:C35=\$O\$5)*(H4:H35=1)*(I4:I35=\$M\$5)*1)*1+...

And also this with sum(if)

Code:
=(SUM(IF(C4:C35=\$O\$5,IF(I4:I35=\$M\$5,IF(H4:H35=1,G4:G35))))
/COUNT(IF(C4:C35=\$O\$5,IF(I4:I35=\$M\$5,IF(H4:H35=1,G4:G35)))))*1+...

Is there a shorter formula that I'm not seeing ? I'm also confronted with the need of adding an "ISERROR" in the formula because not all ranges have the 1-4 numbers in column H.

## SUMIF + AND Statement..

Jan 29, 2010

I'm trying to complete a SUMIF as well as an AND statement. But I'm running into problems. Attached is my spreadsheet to make it easier to follow. Sheet DB!DK13 is my formula. What it's suppose to get is the average of all Males test scores. I've got the SUMIF formula down, that's not the problem. The problem is it's calculating all the 0's in the range as well which produces inaccurate results.

I'd like to maybe add an AND statement to state if the range ('Old and New Standards'!I:I) is >1 then average the results of just Males. I've also added an IF statement to show a null value if the cell is equal to 0 but I'm not getting why it's still averaging my blank cells.

## Formula- To Pull Cell Values Similar To A SUMIF Function (SUMIF(range,criteria,sum_range))

Oct 25, 2007

I am trying to pull cell values similar to a SUMIF function (SUMIF(range,criteria,sum_range)). For example, in A1 I use a data list created from data elsewhere on the spreadsheet. In the data I created elsewhere, there are 2 columns being used. The 1st column is the information that is being used to create the list and the second column contains specific values (number or text). In the dropdown menu I select an available value (text or number) . When I have selected that value I would like cell A2 to show what the cell directly to the right of it shows from the data I have elsewhere in the spreadsheet as mentioned. I have tried the SUMIF function however it seems to exclude certain values (number or text) and I am not sure what else to use.

## SUMIF Statement With A AND Condition

Jun 27, 2006

I have a problem with a SUMIF statement. I think it is =SUMIF(AND(...

I have attached I want to sum in column E lines 255-316 if in cell D 34 and if cell B25 equals GL and cell C255 equals 0001.

Maybe this would be handled better with a sumproduct formula...

## SUMIF In A Column: The First Instance Of Each SUMIF

Apr 21, 2009

I have many kitchens using the same recipes. I need to distill information down until I've got a summary of how much is being made. Uploaded is a condensed version of the point in the process I'm having difficulty with. This workbook will pull information from 8 other workbooks and give me excatly what everyone made on any weekday.

And from there, with the kind help of this forum, I figured out how to do a SUMIF based on the recipe number. And it summed up all instances of 'Recipe X' being used. However, it continues to SUMIF itself all the way down the page... which is good, because of how recipes are chosen for each kitchen. However, I only need to report one instance of each recipe.

In the uploaded example (and I apologize for the colorful sheet, but it helped me double check what I was working on.) ... I only need to report the PURPLE results elsewhere... the first instance of each SUMIF.

## SUMIF And SUMIF Not - Using Two Formulas In One Cell

Feb 27, 2012

I am wanting to use these two formulas in one cell. Is there anyway to do this? If "AD3" is 0 I want this =SUM(X3:AC3) and then if cell "AD3" is greater than 0 I want to basically use this formula

Is there anyway to merge these two formula's?

## Multiple Sumif( )

Jun 14, 2009

I have an old version of Excel and want to use sumifs()

This means I want to add data based on 2 conditions. What should I do?

## Multiple Sumif

Aug 9, 2006

How does one add data to a field that has existing data? For example, say I have a list of different people names and want to say the word "visitor" at the end of each name how is that done for an entire list without have to do it one by one. Also how do I add a word to the beginning of a list of names as well?

## SUMIF With Multiple Conditions

Jan 4, 2007

I'm trying to get a sum only if 2 conditions are met.

Column A has dates (ex. 01/02/2007) in a random order. Column B has a location in a text format (ex. London). Column C has a series of values formatted as [h]:mm.

What I'm trying to do is get a sum of the values in column C where the corresponding values in columns A and B = the date and loction I specify. I've tried...

=SUM(IF(('Jan ''07'!A\$2:A\$750="1/2/2007")*('Jan ''07' E\$2:E\$750="London"),'Jan ''07'!J\$2:J\$750))

but it's returning zero values regardless of the criteria being met or not.

## SUMIF Multiple Columns

Jun 15, 2009

I am new to excel and functions I am trying to put together a spreadsheet of computer costs. Basically I am listing various mobos, cpus, psus, etc. I have a column that signifies which mobo/cpu/etc I want to include in the actual total cost of the computer. Currently the only way I can perform the summation is to create a long drawn out calculation, which is a pain if I need to add more rows to the spreadsheet. I want to try to do a summation.

So, the grid looks something like the following:......

## Sumif Across Multiple Sheets

Apr 21, 2006

I have the following formula that I am trying to use but the result is #VALUE:

=SUMIF(Sheet1:Sheet9!B1,"PMI",Sheet1:Sheet9!A3)

The formula should look in cell B1 of each sheet and if it is equal to "PMI"
sum of the value in cell A3 in each sheet.

## Multiple Conditions With A SUMIF

May 31, 2006

My book is telling me that I can't use multiple conditions with a SUMIF
statement but other sources are telling me I can. Either way, it's not
working!

My situation:

Look at data in \$V\$2:\$V\$144 (range = "group"). If cells in "group" =
"Broth" then look at \$Y\$2:\$Y\$144 (range = "bldg"). If "bldg" = "1" then sum
corresponding cells in \$R\$2:\$R\$144 (range = "fcst").

This is one of my attempts - feeble...

=SUM(IF(group="Broths",IF(bldg="1",fcst,0)))

## How To Sumif Multiple Criteria

Nov 27, 2011

I want a sumif where:

=sumif(f2:f500,

if column f has a 7 in it and if column e is greater than 5

## Sumif With Multiple Conditions?

Jan 25, 2012

I am trying to use multiple "ifs" and then summing the cells that obey my conditions.

Let me give an example:

Column A - place
NY
Chicago
Los Angeles
San Francisco

Column B - object
gate
keyboard
pencilcase
cellphone

[code]....

I want the ifs and sumifs to:

Check if place = NY
Check if object = keyboard
Check if condition = failure

and sum the prices when all of the above are true.How do I do it??

## Sumif Over Multiple Tabs?

May 21, 2012

How can I sumif over multiple tabs?? Without using sumif(a:a,sheet1!a1,c:c)+sumif(a:a,sheet2!a1,c:c)

Like sumif(sheet1:sheet2!a:a,a1,sheet1:sheet2!c:c) I do not want to use a macro. I have about 80 sheets.

Sheet1
Apples 93
Pears 49
Oranges 20
Bananas 35

Sheet2
Apples 100
Pears 13
Oranges 2
Bananas 350

## SUMIF Over Multiple Sheets

Sep 28, 2012

I have got a workbook with multiple sheets, each sheet is in the same format but with different products across the top. The below formula sums the relevant columns by looking for the column with the relevant product code then sum accordingly. How do I amend the formula so that it does the sum for multiple sheets. I could just copy the formula for each sheet but their must be a better way to do this.

Code:
=SUMIF('FM'!\$A:\$A,\$A2,INDEX('FM'!\$A\$1:\$J\$34,0,MATCH(Summary!B\$1,'FM'!\$1:\$1,0)))

## Sumif For Multiple Row And Column

Aug 29, 2013

How to use formula approach to get the total figure for A & B

A
1
2
3

B
4
5
6

[Code]....

## How To SUMIF Multiple Columns

Apr 23, 2014

I have to SUM vendor totals in my spreadsheet and SUMIF worked perfectly, but only for one column of expenses. I need to sum over fifteen columns but searching told me that SUMIF / SUMIFS will not work. Is there another way?

## Sumif Across Multiple Columns

Jun 30, 2014

I'm trying to sumif across multiple columns, only if there is NO data in the first cell of the row. Column A is not in use - B contains some blank cells and some with data. In columns C:AM I want to sum all data in rows where the cells in Column B are blank.

Code:
=SUM(SUMIF(\$B\$2:\$B\$191,"",C2:C191),(SUMIF(\$B\$2:\$B\$191,"",D2:D191)),(SUMIF(\$B\$2:\$B\$191,"",E2:E191)),
(SUMIF(\$B\$2:\$B\$191,"",F2:F191)),(SUMIF(\$B\$2:\$B\$191,"",G2:G191)),(SUMIF(\$B\$2:\$B\$191,"",H2:H191)),
(SUMIF(\$B\$2:\$B\$191,"",I2:I191)),(SUMIF(\$B\$2:\$B\$191,"",J2:J191)),(SUMIF(\$B\$2:\$B\$191,"",K2:K191)),
(SUMIF(\$B\$2:\$B\$191,"",L2:L191)),(SUMIF(\$B\$2:\$B\$191,"",M2:M191)),(SUMIF(\$B\$2:\$B\$191,"",N2:N191)),
(SUMIF(\$B\$2:\$B\$191,"",O2:O191)),(SUMIF(\$B\$2:\$B\$191,"",P2:P191)),(SUMIF(\$B\$2:\$B\$191,"",Q2:Q191)),

[Code] ...........

Jan 18, 2007

I am performing a sumif to look to see if a number starts with #, then another to see if the the class is text, both are fine and both return values of 1 if it meets the criteria. My problem is i then need to add a sumif (or something im not quite sure wot) to add the cost if the number begins with a # and the class is text (so baically if both the other sumifs = 1).

## SUMIF Of Multiple Values

Jan 8, 2008

I want to do a SUMIF formula and add only certain words in column A.

Column A has a list of airlines in it, and column H has a list of flight durations. I want to add together all of the flight times but for only a few airlines.

I tried to put OR in the middle of the SUMIF function, but it didn't work:
=SUMIF(C:C, "Air Canada" OR "Air Tahtiti Nui", H:H)

How would I get it to add together all of the flight times for Air Canada and Tahiti Nui, without having to put many SUMIF formulae?

## Multiple Sumif Array

Feb 7, 2008

I am preparing an analysis of my company's expenses for the last two years by account. One on excel sheet I have the raw data,

bank account, payee, payment date, clear date, amount, payment type

(This is from all accounts for the whole 2 year period.)

In the second sheet, I have a summary schedule setup. The summary is setup like this:

Account #
Row 1: Begin Date
Row 2: End Date

Payment Type 1 AAAAAAA
Payment Type 2 BBBBBBBB

I would like to come up with a formula that will populate AAAAA & BBBBB for a given account during a given time period. For example, I would like it to give me the total of Type 1 payments from account 12 during the period January 2, 2005 through January 27, 2006.

Right now it is only giving me the info for 1 payment type and 1 account. Here is what I have now {=SUM(IF(Detail!\$G\$10:\$G\$3942>=R\$5,IF(Detail!\$G\$10:\$G\$3942

## Sumif With Multiple Items

Mar 25, 2008

I am using a sumif formula where if column b is a certain number then it will add up the value in column e.

This works fine, however now I wish to add another filter, which is only for it to add the values in column E if the value is b is correct (as above) and also if column c has a value of 100.

## Sumif - Multiple Criteria

Nov 12, 2008

Column A |Column B |Column C
Store_Name| Dates |Revenue_Generated

I want to sum the revenue generated in every row where:
Store_Name = "x"
Dates >= Date1
Dates

## Sumif On Multiple Ranges

Jan 29, 2008

I want to SUMIF on 3 columns of data - that is :

=SUMIF(\$C\$3:\$C\$217,A9,\$F\$3:\$H\$217)

But this only returns the data in column F, rather than summing the instances in all 3 columns.

Is it the case that I must use multiple SUMIF functions with each range separated?

## Adding Multiple Cells From Multiple Sheets With Sumif Function

Jan 20, 2009

I'm trying to put together a spreadsheet that tracks disc capacity increases, affected by any incoming projects. I've managed to do so for one project, but would like to for up to 10. The way i've designed the solution (i'm sure there are far more elegant ways, but hey) is thus:

A forecast worksheet keeps track of a grand total, taking information from sheets P1 -> P10 (being projects 1 to 10). I am unable to figure a way to add up all the increases from all 10 project worksheets with one succinct formula. What I use so far is: ='P1'!C83+SUMIF('P1'!E82,"=2009 - Q1",'P1'!D82) ..................