```
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

- it is numeric
- 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)
```

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...

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"....

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,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,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...

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?...

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...

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....

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,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...

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...

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...

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...

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...

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...

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...

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...

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...

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")...

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:...

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,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...

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...

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...

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...

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...

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...

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...

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!...

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...

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...

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...

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...

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...

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,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,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,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...

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...

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...

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...

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...

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,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...

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...

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,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...

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...

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.