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?
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.
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 RelatedI 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 RelatedI'm trying to use DGET to pull coordinate pairs from a list and match them up to a label based on certain criteria. I can't seem to get it to work. I'm also thinking that the way I have it set up, the coordinate pairs might not stay together and that has to be the case. My criteria is multiple cell based too and I don't think DGET can handle that.
For example, In order for a coordinate pair to be labeled "B" the x coordinate must satisfy a condition and the y coordinate must satisfy a different condition. Can I use DGET with the AND function?
I have a list where from which a summary is made but i need true / false thru formula once it is confirmed that it is reconciled with main data.
I have attach a sheet and explain what i needed in red highlighted.
Book1.xlsx‎
I do have an excel workbook containing two sheets:
Sheet1 contains a list of 30 criteria, every one with its own check box (TRUE/FALSE).
Sheet2 contains a database of items, which columns contain the 30 criteria mentioned above. (So sheet1.criteria1 can be the only value of sheet2.column1)
I am trying to program a macro, which can be assigned to kind of a "submit"-button in the first sheet. Its purpose should be to open a new third sheet and listing all the items from sheet2, which contain values checked=TRUE in sheet1.
I need to get together a countif formula that only counts if BOTH specified criteria are true... This is what I thought would work:
Code:
=COUNTIF(W1:W28, AND(>A18,
I'm trying to find the sum of a range of values based on multiple criteria, and the criteria is that the fields all have to be identical, then sum them. I've attached a brief example spreadsheet that has the fields
A=City
B=State
C=Values
What I want the formula to do is first find the range of all the matching states, then find the range of all the matching Cities within the states, and then sum the values based on them having matching city values.
I've been able to do that with one criteria using SumIf, I'm not sure if this will help paint an image of what I want to do:
=ROUND(SUMIF($B$3:$B$11,$B$3:$B$11,$C$3:$C$11),0)
But I can't figure out the way to do multiple criteria against itself. Most of the results I get from Google using multiple criteria are using a set few values, and I can't seem to figure out how to alter those methods to work with my situation.
I have two columns with values. Then I have a third column with one letter A or B.
I'm not used to excel, but I've tried my way with COUNTIFS and I'm pretty sure it's the way to go, but I'm lost in the syntax.
I want to count the number of times the values in the first column is larger than the values in the second column, if the letter is A. And then flip the ">" sign and count that and hopefully the first number is higher.
I am trying to create a formula to count the number of time the word "NEW" appears in one column, provided the result of another column gives a certain answer. Sample of my problem and question in detail on attached.
View 2 Replies View RelatedI need to check one column to see if it is less than another one
If it is I need to have the IF function return specific criteria for true and then for false but then I also need it to rely on the results of another question from another column if the result of the first query is less than the figure in another column again I need to return specific criteria for tur and then for false
An AND function requires both to be true but when one isnt what do I write?
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 RelatedIm 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.
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.
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
First one: The thought process is that inbound inventory data is logged on one sheet; outbound inventory data logged on another within the same workbook. When specific data from the inbound sheet (LPN column) is 'shipped' and entered on the outbound sheet, that specific data needs to be verified against the order number it was intended to ship against and if everything lines up, to have a TRUE value returned, or FALSE if not.
Second one: same type of thing - I'd like to look for the same specific criteria (LPN) between the inbound and outbound worksheets and if the same LPN is found on the outbound sheet, to return a value of "SHIPPED".
Third and fourth - same deal again except this time, would like to tie back the outbound Destination and ship date data from the outbound worksheet to the inbound worksheet.
A copy of the workbook is attached for reference with cells that require formulas highlighted in yellow on the inbound sheet and data cells from which the formulas will verify data in green on the outbound sheet.
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‎
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).
is there away to do the sumif formula with 2 criteria? See attachment
View 3 Replies View RelatedNormally, 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?
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?
I want a sumif where:
=sumif(f2:f500,
if column f has a 7 in it and if column e is greater than 5
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
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 RelatedI 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
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'
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 RelatedI 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)
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.