excel-formula,excel-2007,formula , Excel: Sum if the value is numeric and meet a relative criteria


Excel: Sum if the value is numeric and meet a relative criteria

Question:

Tag: excel-formula,excel-2007,formula
     A    B        C
1    N1   4        3
2    N2   7  Blocked
3    N3   5        5
4    N4  12        0

I want to SUM the total of column "C" if

  1. it is numeric
  2. if it is less than column B

I want it to be something like this, but obviously it doesn't work:

=SUMPRODUCT((C1:C4<B1:B4)*IF(ISNUMBER(C1:C4),C1:C4,0)))

This works when there is no Text cells:

=SUMPRODUCT((C1:C4<B1:B4)*(C1:C4))

This does a count instead of a sum:

=SUMPRODUCT((C1:C4<B1:B4)*ISNUMBER(C1:C4))

Please help.


Answer:

Aha! This is actually quite simple and straightforward after reading Excel SUMPRODUCT Function. Thanks to the author Dave Bruns!

The formula is

=SUMPRODUCT(--(C1:C4<B1:B4),--(ISNUMBER(C1:C4)),C1:C4)

Related:


how to setup a excel chart with data points


excel,excel-formula
I have a excel spreadsheet that has been exported from a kendo-ui grid. I have zero experience with excel and I need help turning this data into a excel chart. the attached spreadsheet is what i am working with. Every subdivision name is its own series with (sqft,price) points. is...

Increment count in column based on value in column


excel,google-spreadsheet,excel-formula,array-formulas
I've 2 columns A and B. A contains names and B contains the count of those names. Ex: let A1 have value "fruits". Then B1 must have value 1 because this is first time "fruits" is present in A. Next A2 has "flowers", and B2 will have 1 against "flowers"....

pulling and comparing dates in excel


excel,vba,if-statement,excel-formula,excel-match
For example: On my first tab Last visit, I have columns, Customer Name, Customer # and Scheduled date On my second tab, All visits I have Customer Name, Customer #, and Visit Dates. Now, this tab shows all of the visit dates (even the last one that is on Last...

Excel Pivot Table Exclude one Row in Calculated Field


excel,excel-formula
In Microsoft Excel I want to have a pivot table that uses a calculated field for a row. I would like the calculated Row to exlude the current year. So I want two summary rows one with current year and one without. The attachment in orange shows my desired result....

Excel Formula - IF & “X” > 0


excel,excel-formula
I have a formula that I'm using in Excel to determine what the payback of a value is after "X" of months. But I'd like to modify the formula to display 0 or $0.00 if the result of the formula is less than $0.00 The current formula is =C2-(C2*(G2/24)) But...

is there a shortcut to open filter>contains box in excel?


excel,excel-formula,excel-2007
I use filters a lot. Filter > contains option But it is slow because everytime i need to do several clicks. So, my question is, is there a shortcut to open filter, drop down, contains window?...

Horizontal Index Match (returning column header)


excel,excel-formula
A B C 1 Fruit Color Meat <- Column Header 2 Banana Red Pork 3 Apple Black Chicken 4 Orange White Beef From the table1 above to table2 below: A B 1 Name What? <- Column Header 2 Banana Fruit <- Formula should return these values, based on table...

unable to use MIN and IF function in excel


excel,excel-formula,excel-2010
Age Qualification 19 Higher Secondary School 24 Graduate 25 Post Graduate or above 21 Graduate 17 Higher Secondary School I am trying to get Minimum age of graduate student which is 21. The incorrect formula i used is =MIN(IF($B$2:$B$6="Graduate",$A$2:$A$6)) which gives 17 as min value....

Using OR in SUMIF Excel


excel,excel-formula
I have a curious question about using the "OR" in an excel SUMIF. Basically, in a SUMIF(A:A,"Cat" OR "Dog", C1:C10) How can I use the OR function in an excel SUMIF function?...

Excel INDEX isn't working with Table reference


excel,excel-formula,excel-2010
I am using Excel from Office 2010. I am trying to get values from a specific row (or, eventually, rows) in the table, found by running a search. I can successfully identify the row in the table that I want to output the data from, but the INDEX function is...

Vloopup + INDEX and transpose


excel,excel-formula
I really need help with the following situation . I'm trying to use a vlookup or index that I believe is really advance for me , unfortunately I don't have the knowledge how to attached an excel file here so I will add a image I have a huge data...

How do I match a date from a list of dates in excel?


excel,excel-formula,comparison
I need to compare two dates to see if they are equal from a list of dates in a column. Eg. Sheet 1 Column A Date 1 Date 2 Date 3 Date 4 Sheet 2 : Row F: Date 5 I want to see if date 5 is equal to...

How to insert excel formula to cell in Report Builder 3.0


sql-server,excel,reporting-services,excel-formula,ssrs-2008-r2
There is RDL report template for SQL Server Reporting Services. I need to set value for cell in table in the report template which must be calculated from other values in the report. When the report is exported to Excel file I need to see the Excel formula in that...

Converting Date Into a specific text format in excel


excel,excel-formula,excel-2010,excel-2013
Ok so i have been trying to do this and i pretty much keep on failing :p Basically what i want to do is take the date 10/1/2013 and turn it into 201310. I tried to concatenate using left and right. tried to make it into text using left and...

Sum row based on criteria across multiple columns


excel,excel-formula,array-formulas
I have googled for hours, not being able to find a solution to what I need/want. I have an Excel sheet where I want to sum the values in one column based on the criteria that either one of two columns should have a specific value in it. For instance...

Sum the values from the days in a specific week in Excel


excel,excel-formula,week-number
So I have some rows of data and some columns with dates. As you can see on the image below. I want the sum of the week for each row - but the tricky thing is that not every week is 5 days, so there might be weeks with 3...

Ranking with subsets


excel,excel-formula,rank
I'm trying to rank values and have managed to work out how to sort ties. My data looks at the total number of entries, ranks based on that and if there is a tie it looks to the next column of values to sort them out. However, I have two...

Creating dynamic dropdowns in Excel where values may appear in more than one column


excel,excel-formula
Normally, where the values in the column of a lookup array are unique there is only a need to match the value in the last dynamic data validated list with the value in the relevant column of the lookup array to provide the range of values for the next dynamic...

Aging formula without using additional column


excel-formula,countif
I have a table like below ID StarDate EndDate 123 6/5/2015 15:54 6/12/2015 15:54 124 6/6/2015 15:54 6/8/2015 15:54 125 6/7/2015 15:54 6/9/2015 15:54 126 6/8/2015 15:54 6/11/2015 15:54 127 6/9/2015 15:54 6/15/2015 15:54 I want to get the total count of aging without using any additional column 0-30 =COUNTIFS(A:A,">124",A:A,"<127",TODAY()-B2:B6,"<=30")...

Counting across rows for ###, then countif down columns for ids matching ###


excel,excel-formula,countif
I'm trying to count the number of activities each organization has done in my dataset. Right now, each row represents a single organization's list of activities. The following formula accurately finds the number of activities per organization: =IF(COUNTA(A1:H1)=5,"yes") However, I now need to group organizations by amount of activities (ex:...

Counting values embedded in strings inside a column (Google Spreadsheets)


excel,google-spreadsheet,excel-formula,formula,countif
I have a Google Survey where I created some multiple choice questions. Now, I am trying to count the responses. [A] [B] [Response#] [Selections] [1] [Apple,Orange,Banana] [2] [Orange,Banana] [3] [Apple,Orange,Banana] [4] [Banana] [5] [Apple,Banana] [6] [Apple,Orange] . So on my summary spreadsheet, I would like to have the totals: [Favorite...

Excel lookup by text


excel,excel-2010,excel-2007
I am trying to make lookup for excel table using the text in the columns For example if i have A1 | A2 BMW-X6 | 1 BMW-X5 | 1 Mazda-1 | 2 Mazda-2 | 2 Mazda-3 | 2 Mercedes-Benz | 3 Mercedes- cls | 3 I want where there is...

How to set column conditionally in Excel?


excel,excel-formula
I have been working on a spread sheet for work for about a week and a half now and have been stuck in the same place. I am working on something for payroll purpose. So A1 would have an employee name as would the rest of the column. B2 would...

INDEX/MATCH for closest value to a certain date


excel,date,excel-formula,worksheet-function,array-formulas
In sheet "Dividends" I have a table with dividends sorted by daily dates. E.g, columns A and B contain the following entries: 6/14/2015 6/13/2015 6/12/2015 0.045 6/11/2015 6/10/2015 This means that the stock paid a dividend of 0.045 on 6/12/2015. In another sheet "AdjClose", I have a table with weekly...

How to create a dynamic table in Excel?


mysql,excel,excel-vba,excel-formula
I am trying to create a dynamic table - I have tried a Pivot Table, but cannot get it to work. So I thought that maybe it could be done with an IF-statement, but that did not work for me neither. Basically, I have 2 tables, 1 table containing the...

Excels INDEX MATCH - Finding multiple matches


excel,excel-formula
------A ----------------- B ----------------------C ------------------------D 1 --First--------------Last-----------------Start Date--------------End Date 2 --John--------------Smith--------------08/08/2014------------01/01/2015 3---John--------------Smith--------------08/11/2014------------17/11/2014 4---John--------------Smith--------------06/06/2014------------23/12/2014 5---Abel--------------Jones--------------14/05/2014------------29/04/2015...

Program flow unexpected behavior


excel,vba,excel-vba,excel-formula,breakpoints
I am running the following Sub, which inside other two subs: Sub SalvaDadosLogin() 1: ThisWorkbook.Worksheets("Registro").range("a11") = loginComdinheiro 2: ThisWorkbook.Worksheets("Registro").range("a12") = senhaComdinheiro 3: ThisWorkbook.Worksheets("Registro").range("a14") = salvaSenhaComdinheio 4: ThisWorkbook.Save End Sub Sometimes when I run this sub my VBE starts executing a user defined function (after running the line 1). Other times...

Changing the active cell


excel,vba,excel-vba,excel-2007
I was looking to create a program that examined a column of text in excel and extracted the first line that contained currency. The currency was in Canadian dollars and is formatted in "C $##.##" with no known upper bound but unlikely to reach 10,000 dollars. I was hoping to...

How to count multiple values in one single cell using Pivot Tables in Excel?


excel,excel-formula,pivot-table
I have this table in a Excel sheet: I would like to create a Pivot Table to have this outcome: Do you guys have any idea how to do it? Thank you!...

SUMPRODUCT combining partial text and dates


excel,excel-formula
In an excel sheet I am trying to retrieve how many entries of a given name occur within a month. I have the date with my month stored in N48 My spreadsheet would look like: B AE Test 06/04/15 Testing 06/05/15 Test 06/08/05 Name2 06/04/15 Name2 06/02/15 So far I...

Is there any formula to create a hyperlink in an excel to another sheet?


excel,hyperlink,excel-formula
Here is what i exactly need to do. I have an excel workbook with many sheets. I need to work on two sheets- Attributes_Master and Domain_Details. Both have a field in commmon - Domain but not in same order. I need to add a hyperlink in Domain column of Attributes_master...

Get the maximum values of column B per each distinct value of column A


excel,excel-formula
I have an Excel spreadsheet of the form: A,B X,1 X,5 Y,4 Y,11 X,7 Z,1 I would like to get the maximum value of column B for each distinct value of column A - how can I do it via pure Excel formula? Desired result (order is irrelevant): A,B Y,11...

Determine longest date range, from two date ranges - Excel


excel,excel-formula
I have the following excel table ------A ----------------- B ----------------------C ------------------------D 1 --First--------------Last-----------------Start Date--------------End Date 2 --John--------------Smith-------------10/09/2014------------24/11/2014 3---John--------------Smith--------------20/11/2014------------31/01/2015 (Occasionally I have duplicate names on a spread sheet). I am creating a formula which determines the number of days between the earliest start date and latest end date from two date...

INDEX MATCH obtaining values for duplicate names


excel,excel-formula
I have one example table with the following data in Sheet1 with the following random data ------A ----------------- B ----------------------C ------------------------D 1 --First--------------Last-----------------Start Date--------------End Date 2 --John--------------Smith--------------08/08/2014------------01/01/2015 3---John--------------Smith--------------08/11/2014------------17/11/2014 4---John--------------Smith--------------06/06/2014------------23/12/2014...

Is there a way to sum values to the right of a + symbol across many cells?


excel,excel-formula
I am normally well versed in Excel but I am having a blank on this one. I have a bunch of formulas eg: VLOOKUP($C22, 'Pivot Table'!$A$4:$DD$400, 13, FALSE)+3162 VLOOKUP($C22, 'Pivot Table'!$A$4:$DD$400, 22, FALSE)+18 VLOOKUP($C22, 'Pivot Table'!$A$4:$DD$400, 31, FALSE) VLOOKUP($C22, 'Pivot Table'!$A$4:$DD$400, 40, FALSE)+8.6 and so on What I need is...

Excel - How to concatenate 2 values to make a refference


excel,excel-formula,excel-2013
I have a long column of data (15000 values) that simplified looks like this: A B C D 1 lorem pellen Vestibulum 2 epsum tesque pretium 3 Morbi vel convallis 4 fermentum tellus nibh 5 Interdum molestie Vi .. 15000 Then I have a second table: A B C TYPE...

Excel return value or run calculation if criteria is not met. Formula needed


excel,excel-formula,formula
This is a fairly simply formula that I need syntax for. I do not know the syntax that Excel will accept. Formula: I want to return a value of "0" if the target cell has a value of "2" or less. If the value is greater than "2" I want...

Excel with AND, IF, MAX


excel,if-statement,excel-formula,max,excel-2010
I can't quit figure out what I am doing wrong here, I know how to do the formula in multiple steps but can not figure out how to do it in one step all together. So what I am trying to do here is in column A there is numbers...

Lookup value next to the first number in column


excel,excel-formula
I need a formula that can find the date in a cell next to another that has the first number in the column. In column A I have dates and in column B onward I have stock prices. The dates goes back to 1990 daily, but not all the stock...

Can Not Get My VLookUp In Excel To Return The Requested Data


excel-formula,vlookup,excel-2013
Can Not Get My VLookUp In Excel To Return The Requested Data I am trying to pull data from another sheet based on data selected from a dropdown on the main sheet. All the formatting is "General" =VLOOKUP(F15737,'Location Master'!$A:$J,2,FALSE) It just keeps returning me #N/A...

sumifs on excel linked sql server query with date


sql-server-2008-r2,excel-formula
I'm having no luck being able to do sumifs or countifs on a cell range that is linked to an MS SQL server 2008R2 view when using the date column as a criteria. If it is done on a native table link they works fine but it appears that the...

Get unique values in a column except values filtered from the original column - Excel Formula Only


excel,excel-formula
I have been using =IFERROR(INDEX($F$13:$F$1353, MATCH(0,COUNTIF($X$12:X12, $F$13:$F$1353), 0)),"") and it work perfectly in order to bring unique values from a column with repeated values. However I use a filter on the repeated values column "F" and when I deselect any item from the "F" filtered column I would like to...

Dynamic formula or vba to offset a vertical list by one, and move the bottom of the list to the top?


excel,vba,excel-vba,excel-formula
Here is what I am trying to do, I have a sheet that has a list of names with no duplicates that varies in length. I would like to have either with a formula or vba sub, to have the next row copy the original list of names and offset...

Excel search and match two cells in the same row for multiple rows


excel,excel-formula
I am attempting to accomplish a look-up in Excel and I can't seem to find anything about it online. Admittedly, I may not be searching for the answer in the right way. So if this is redundant and has already been answered I apologize upfront. I'll attempt to describe my...

Always Return the Previous Saturday's Date Using NOW Function in MS Excel 2007


excel-2007
I have a workbook that I am using the NOW function to return today's date and then I have another cell to convert to WEEKNUM that I use to MATCH on with a different tab. I need to be able to create a formula to read today's date and always...

Converting letters to text and back again (Excel 2013)


excel,excel-formula,formula,vlookup
For a piece of coursework I have to complete a register of student grades. I am trying to calculate their overall grades by converting their grades from each of the four units into numbers (which I have done using the VLOOKUP function), but I need to then convert the result...

Excel - list entities (items)


excel,count,excel-formula
Friends, I need to list all the unique entities (items) in a Excel spreadsheet, how to? E.g. **List1** 1 1 1.1 2 a a aa b c c And I need a certain function that returns: **Entities** 1 1.1 2 a aa b c Then I can count the occurence...

Finding a specific cell contanining value greater than the desired value


excel,indexing,excel-formula,vlookup
I'm looking for a way to return a specific cell value from a range based on an initial desired value. It looks like this: DesiredValue typed in cell: | 125| Search Range: line1 RangeA|RangeB line2 1 | 50 line3 51 | 100 line4 101 | 500 line5 501 | 1500...

Returning just the date portion of =NOW()-(WEEKDAY(NOW(),1)) in Excel 2007


excel-2007
I need to return just the date portion of the following formula: =NOW()-(WEEKDAY(NOW(),1)). Any ideas? I am using this formula to get the current date and then return the previous Saturday's date, but I am also getting the time component and I need to drop that portion.