# Nesting A New Criteria In Sumif

Feb 13, 2009
I have the following Formula:

=SUMIF('Sheet2'!C:C;Sheet1!B3;'Sheet2'!E:E)

In this way I got the sum of all items located in column E wich have the same value than in B3 in Sheet 1....

How could I change the formula in the way that only returns the Values in Sheet2 E:E when (If equals to B2 and C2 in Sheet 1 then Sumif)

The value which I want to use to filter is in the same columns in both sheets

View 9 Replies
ADVERTISEMENT
Dec 31, 1969

I want to put a formula in a spread sheet that if block f5 is equal to or higher than 20% of block b4 then block d3 * .0 if less than then * by .0078

View 14 Replies
View Related
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.

View 9 Replies
View Related
Mar 24, 2014

I've attached a sample workbook to show what I am trying to do. I would like the formula to say "if Sheet2!A:A is "MON" and if Sheet2!R:R matches Sheet3!A:A, and if Sheet2!I:I doesn't match any of the values from Sheet1L:L, then I'd like the sum of Sheet2!F:F. It seems pretty simple but I've tried a million different variations of SUMIFS, SUMIF, IF, AND, etc. and I can't figure it out.

View 1 Replies
View Related
Nov 25, 2013

I am trying to set up a sumif statement with two criteria where if the second criteria is true, the total in the sum range returned is divided by two.

I currently have this:

=SUMIF($G$6:$G$41,"digital",I6:I41)

I want to add in "digital/creative" as a second criteria (from the same criteria range) but I only want 50% of the result of these to be totalled.

is this possible?

View 6 Replies
View Related
Feb 6, 2009

I am trying to take a census by date and name for every half-hour using the attached data. So for example, I would like to know how many people Roger had that walked-in and walked-out for each date on the spreadsheet and during what hours. The census would be every half-hour and not double count times. I could actually deal with just the min and max time for the day (by date and name) and having some indicator (say a one or an X) in the cells when Roger had at least someone anytime between walk-in and walk-out. It's kind of like getting a "time worked" based on the data that's provided to me. So I know how many hours Roger worked by day.

View 4 Replies
View Related
Jun 15, 2009

Im using a formula to identify a job description then add up the quantity which works fine. =SUMIF(Master!A1:A10,"Bread",Master!C1:C10)

But my problem is, I have a second description in column 'B' for example called 'White'. I've been trying to get the formula to look at column A & B and if they match criteria in formula, then add up C. But can't seem to get it to work with the second criteria.

View 2 Replies
View Related
Aug 23, 2009

I have a workbook with several different worksheets. I am attempting to pull data from a worksheet, I will name it sheet1. Sheet1 has 6 columns. The data I am interested in is in B and F. Column B has account numbers ranging from 1 - 1,000,000 and column F has the total account balances. What I am attempting to do is to sum the data in F that falls between a criteria in B. For example I want the sum of F that correspond to accounts 4999 thru 7555.

I have tried several variations of the same formula from my online search and am still unable to get it to work properly. I have tried using ">=4999" "<=7555" and I cannot get it to work properly. It seems fairly straight forward, but I am perplexed as to what I am doing wrong.

View 2 Replies
View Related
Jan 8, 2009

I don't know why I can't figure this out, but it has been too long, but I need a formula that looks for yesterday's date and adds up any time that matches that date and that person from the list. So like in this example, for yesterday, my answer would be 1 hour for Ashley.

A B C

1 1/7/09 Ashley 0:10

2 1/8/09 Ashley 0:10

3 1/9/09 Bob 0:10

4 1/7/09 Ashley 0:50

View 9 Replies
View Related
Dec 28, 2013

I need a formula which can sum the mode of payments separately occurring on different dates.

See the attachment. It is like

table 1

date cash credit card

1-1-14

2-1-14

3-1-14

table 2

product booking date booking amount payment date payment amount form of payment

a 1-1-14 100 2-1-14 100 credit card

b 1-1-14 150 3-1-14 150 cash

c 2-1-14 200 3-1-14 200 credit card

I want to add value in table 1 from table 2. In front of date CELL Both cash and credit card amounts should appear separately.

Book1.xlsxâ€Ž

View 11 Replies
View Related
Nov 2, 2008

I'm trying to figure out how to get sumif to sum for non-blank entries- e.g.

sumif(A1:A5,"not isblank", B1:B5).

View 10 Replies
View Related
Jul 24, 2009

is there away to do the sumif formula with 2 criteria? See attachment

View 3 Replies
View Related
Jan 29, 2010

Normally, sumif formula works well with problems that have one criteria to take into consideration. With the table that I have, I'm unable to accurately use SUMIF. I'm not even sure if I should use it since what I really need is Average. SUMProduct proves to be even harder to use for me. Here's what I have:

ID CODEStatusSTARTDUEENDTURN AROUND TIME (TAT)

122307Closed1/142/81/3112 DAYS

122265Closed1/142/22/214 DAYS

122265Closed1/142/101/3112 DAYS

122307ATD1/131/21----------

122253Closed1/121/221/229 DAYS

122265ATD1/221/30----------

Other details:

I used =SUMPRODUCT(--($B$21:$B$31=C4),--($D$21:$D$31=$D$3)) to count the total number of a specific task status per ID code, say, closed and ATD.

I manually added TAT that has "Closed" status on a per ID code basis.

Ex:

ID Code# of Closed StatusAverage TAT

12265213 DAYS (14+12/2)

Is there a formula that will give me the average TAT taking ID Code and "Closed" status as conditions/criteria? What about Vlookup, average?

View 3 Replies
View Related
Jul 31, 2007

In the formula (range, criteria, sum_range), I have a fixed range and a fixed summary range for each column, i.e.: ($F$3:$F$805, "criteria", O$3:O$805).

HoweverI am trying to sum up units by income level (columns D, E, and F) using information from elsewhere on the sheet. I am doing this for each city, which entails changing the criteria for all of the cities I am using three times (once per column).

Is there a simple way to autofill the criteria? They are just names of cities, all in the same column, COLUMN B. Or do I have to type each individual change?

View 10 Replies
View Related
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

View 3 Replies
View Related
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

View 9 Replies
View Related
Jan 19, 2009

I need to add amounts in column B, based on column A’s account numbers, so I want to add only account numbers, say 17101 & 17201 and nothing else.

View 9 Replies
View Related
Sep 24, 2009

I am trying to extract some data from a large spreadsheet and having problems...

Column C contains text descriptions, e.g 'Description One', 'Description Two', 'Description Three'

Column O contains a date.

I need to count the number of items that have a date prior to 1st April 2007 and have certain text contained in the full text string value in column C e.g. 'One'

View 9 Replies
View Related
Aug 8, 2006

I want a formula to sum all values if two different criteria match. I've attached a sample below. The TaskID and the Time Entry ID are the two data points I'm searching for and the totals column is where my sum statement would be. The numbers are being pulled from another worksheet.

View 7 Replies
View Related
Dec 13, 2006

I just became a member of your forum, so please forgive me if I am not concise enough or take too long.

I imput the number of hours associated with week ending dates.

A C D

Date Hours OT

01/08/2006 6 2

03/12/2006 8

04/16/2006 3 1

I need to add up the hours for each quarter for Hours & OT

Jan-Mar

Apr-June etc

This is what I came up with, but it doesn't give me the correct value

= SUMIF(A3:A75,"<="&DATE(2006,03,31),C:C)

View 9 Replies
View Related
Apr 28, 2014

I am trying to get a SUMIF formula to work where the following: - RANGE = Column N:N (These are all a list of dates) CRITERIA = Cell C20:D20 (These are the dates i want to look at) SUM_RANGE = Column P:P

Basically, I want to SUM all the numbers on Column P where the date in Column N falls between and including the dates in Cell C20 and D20

In attachement, I want to show in the YELLOW cells the SUM of Column P where the Date in Column N is between the Dates in column C and D.

View 3 Replies
View Related
Jan 30, 2014

I'm trying to create an overview which shows a breakdown of the total minutes for each task on each given day.

I need to get a formula to work in the grey area which sums the total minutes (column C) based on matching two criteras of task (column A) and day (column b).

View 5 Replies
View Related
Mar 3, 2014

I'm looking for a way to write this:

[Code].....

as this :

[Code] .....

Where the criteria runs across a range (G to BG) that can sometimes have columns deleted, but the range must remain fixed.

View 3 Replies
View Related
Jun 9, 2009

I've searched the boards & haven't found anything as uncomplicated as what I'm looking for. I know I could use Sumproduct, but for something this simple is there anything I can do with Sumif? This in effect is my worksheet.

AB

1AD200=SUMIF('Sheet2’!B:B, A1, 'Sheet2’!C:C)

All I want to do is change the criteria to be the equivalent of A1 OR B2 (ie checking the range for either match. No way to do that in a sumif? I'm looking for the least complicated, easiest to adjust/edit formula possible.

View 3 Replies
View Related
Nov 22, 2009

On the attached example i would like a formula in F2 which sums all the values where column equals "Smith" and column B equals "Golf European". I'm assuming it would involve the SumIf formula but i'm not sure if this is the best solution when the criteria is in two columns?

View 3 Replies
View Related
Dec 15, 2009

I am using Excel 2003 and need help with sumif with 2 criteria. Please see attached worksheet.

I am trying to find total costs for repective projects if they happend in 2009 only, else leave blank.

In attached worksheet, see column c, row 17...thats what the result should be.

Find All Instances of:

Project A that happend in 2009 and from range c2..c7

Project B that happend in 2009 and from range c2..c7

etc.

and show their sum in c17,c18, etc.

View 10 Replies
View Related
Jan 27, 2010

Can anyone tell me what is wrong with the formula on page 1 cell F56? it is not adding the 666 gallons at the end of column F for 426 - cc. Any ideas?

View 11 Replies
View Related
Feb 1, 2010

I would like to take a precise value from one table which corresponds to two different criteria. See the example attached with this message.

View 3 Replies
View Related
Feb 15, 2010

Im tryng to do a SUMIF formula that has several criteria as answer but using the concatenate formula, but it doesnt seem to come back with the answer - im using AND/OR formula aswell, but have a feeling im doing it wrong.

It does work when i use the single concatenate, but not when i try a do more than one

View 4 Replies
View Related
Mar 4, 2009

I'm trying to return a value from a data dump based on 2 seperate criteria, 1 being the name of a person and the other being the name of an activity. Is there any way of using an array and the SUMIF function to do this?

View 4 Replies
View Related
Jul 22, 2002

The following table is used in the sumif.

I would like the sumif to use the range with the names, and the range with the dates.

I have a GUI in which the user enters a name and a date and the $ amount earned for that week should appear.

The criteria is the name and the date.

******** ******************** ************************************************************************>

Microsoft Excel - Business Book.xls___Running: xl2000 : OS = Windows Windows 2000

(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutP20=

IJKLMN3******4michaelb1*14$400.007/21/20025Michaelb1215$450.007/21/20026

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

View 9 Replies
View Related
Jun 12, 2003

Sumif formula. I need to somehow put a loop on the criteria or something...

The formula is sumif(range, criteria, sum range). The problem I am having is that I have about 20 criteria to select, and sumif is designed for one criteria. I know that I can type the exact same formaula in and have something like this...

=SUMIF(D25:D107,Variables!A6,F25:F108) + SUMIF(D25:D107,Variables!A7,F25:F108) + SUMIF(D25:D107,Variables!A8,F25:F108) + SUMIF(D25:D107,Variables!A9,F25:F108)+ SUMIF(D26:D107,Variables!A10,F26:F108)+ SUMIF(D26:D107,Variables!A11,F26:F108)

This is OK for a few variables but some formulas have multiple criteria in excess of 15-20. the other thing is that the criteria list will grow over time as more variable are added, and instead of changing the formulas throughout the spreadsheet, I would like to have a big range that I can slowly fill up as I go along

The ideal situation would be if I could put a range in ( ie A6:A30) and then when I get a new criteria I just add it to this range. This will make administration eaaseier by giving me one location to update data.

I will show you an example.. I want to calculate the amount of petrol I use based on three criteria - BP, Shell, Caltex ( the criteria being petrol stations that come up on my bill).

Column

A/ B / C/ D

Row / description / Amount/Variables

1 / Shell Petrol / $10/Shell

2 / Cat Food/$13/BP

3 / BP Store/$24 / Caltex

4 / Dog Food/$23

5/Shell Petrol / $98

6/Caltex Petrol/$31

7/BP stuff/$30

8/Shell Store/$70

What I have been doing is this...

=sumif(B1:B8,D1,c1:c8) + sumif(B1:B8,D2,c1:c8) + sumif(B1:B8,D3,c1:c8)

If I could somehow get the formula to choose the whole column D as the criteria range and get the 'sum if' to somehow loop so it goes down the d column and does the same calculation, but just changes the criteria variable each time, then instead of changing the formula, I could just add to the D column each time I had a new criteria I wanted to check.

I looked into DSUM and Pivot Tables. DSUM seems to work well with numbers, not variables and Pivot tables did my head in, especially seeing I know that there is a way to do this in a formula based way..

View 9 Replies
View Related
Apr 18, 2014

A1:A10 = Criteria Range 1

B1:B10 = Criteria Range 2

C1:E1 = Criteria Range 3

How to sumif all 3 criteria are met?

View 7 Replies
View Related
Oct 10, 2007

I am trying to do a sumif function where it only sums when a different criteria is met in several different columns.

eg

sumif b:b shows portsmouth d:d shows calais f:f shows night then sum range g:g

View 9 Replies
View Related
Mar 27, 2009

-I have 3 relevant columns, A, B and C with 999 rows

-Column A is a status indicator "Yes" or "No" are the only options

-Coumn B a list of vendor names, which requires the wildcard example:"*Verizon*"

-Column C is the data range that needs to be summed

Right now i have this:

=SUMPRODUCT(--(A2:A999="No"),--(B2:B999="*VERIZON*"),C2:C999)

But, it seems sumproduct does not allow wildcard matches within it's "--" arrays. I am willing to try something else if possible, but I lack the excel knowledge to answer this myself.

View 9 Replies
View Related
Feb 15, 2010

I want to create a sumif formula that will sum the data if it meets five different criteria. I tied to do an “Or” statement in the formula, but it doesn’t work. For example, I want to sum all the rows that contain: Apples, Bananas, Cherries, Pears, and Plums. How do I write the sumif formula so that it will do this?

View 9 Replies
View Related
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)

View 3 Replies
View Related
Sep 1, 2009

I have a column which lists total spend by vendor. I am using the sumif formulae to sum spend in certain categories, for example spend greater than £500,000. I would like to use two criteria, for example greater than £300,00 but less than £499,999. I am also using countif to get the number of vendors in each range of spend. How do I adapt my current code of "=SUMIF(D2:D1463,">300000",D2:D1463)" to look at greater than £300,000 but less than £499,999. I'm sure this is simple but like most things only simple if you know how.

View 2 Replies
View Related
May 28, 2009

How would I write a sumif function to only add consecutive cells that match the criteria? Here is the formula I am using right now, but it adds all of the cells that match the criteria, not just the consecutive ones. Also they need to be consecutive cells from the current cell (in the formula below i would be referring to A80)

=IF(A81<>A80,SUMIF(A$5:A80,A80,H$5:H80),"")

More data:

Column A is a group of dates, but not in order, they are slightly mixed up

Column H is a monetary value.

View 8 Replies
View Related
Aug 22, 2011

I have a table with

Column A - Suppliers

Columns B to M products with a product group (in row 1)

Prod Group..Core.......Core........Outer........Inner.........Core

Supplier......Type A......Type B.......Type A........Type B.......Type C

AB Ltd........1000.........2000..........500.............750...........5000

CD Ltd........3000.........5000..........100.............950...........8000

AB Ltd........2000.........4000..........600..............800..........7000

I would like to know how to sumif when for eg supplier is "AB Ltd" and the product type is "Core" in this eg = 21,000 (how to paste a table)

View 9 Replies
View Related