Use date values from different sheet in formula
Filip Huysmans
I have two sheets in my excel file.
The first sheet contains values and dates, on the second I want to calculate values between a date range.
for instance:
50 20/9/2014
1600 16/10/2014
254 21/10/2014
1547 03/11/2014
(Belgian time)
I tried this with the following formula:
=SUMIFS(INPUT!$G$3:$G$100; INPUT!$H$3:$H$27; ">=01/10/2014"; INPUT!$H$3:$H$27; "<=31/10/2014")
This gives me an error in the formula and I notice that inside the formula values the date has become a random value:
Anyone has an idea how to do this correctly?
CustomX
I couldn't find it at first either, but it seems your ranges need to be the same size (sum_range and criteria).
So change your sum_range to G3:G37 or your criteria range to H3:H100 and it will solve your problem. :)
Reference: http://www.excelforum.com/excel-formulas-and-functions/731875-sumifs-returns-value.html
Collected from the Internet
Please contact [email protected] to delete if infringement.
edited at
- Prev: How to install a Python package with documentation?
- Next: How to create a CASE statement depending on the result of a Select Statement with parameter?
Related
Related Related
- 1
Use date values from different sheet in formula
- 2
Google spreadsheets: conditional formatting with custom formula based on values from different sheet
- 3
Pulling Values From A Different Row & Column With Array Formula In Google Sheets
- 4
Excel Formula to Calculate Percentage on Different Sheet
- 5
Formula - Get sheet name using cell values
- 6
Use a reference from one workbook in a formula of a different workbook
- 7
Google sheets: How to use the CLEAN function on referencse from a different sheet
- 8
How to use a sheet name that is referenced in a cell in a formula?
- 9
read formula from other sheet with phpexcel
- 10
Is there a query formula in spreadsheets where if the date in the column matches todays date, it pulls the values from the rows within that column?
- 11
Formula - find and copy from sheet 1 to sheet 2
- 12
Excel: Is it possible to Lookup multiple values in a different sheet and use the Sum function to add them together?
- 13
Excel: VLOOKUP from a different Sheet
- 14
Excel IRR Formula with non-contiguous ranges on a different sheet
- 15
Copy the formula to another sheet and paste it as values to specific columns
- 16
Counting values from excel sheet
- 17
Query values from separate sheet
- 18
Counting values from excel sheet
- 19
Excel-VBA transfers incorrect date values from SAS to Excel-sheet
- 20
Different values for datetime, date and time fields from timestamp in MySQL
- 21
I need to copy format and values from a partial row to a destination in a different sheet (same spreadsheet), function chaining problems?
- 22
Excel MEDIAN formula for values based on date
- 23
How to change values in excel acording to different sheet
- 24
VBA - copying unique values into different sheet
- 25
How to I use a selectable field to change a sheet reference in a formula?
- 26
How can I use a formula to match multiple criteria from a different table in Excel?
- 27
Formula to convert UTC date time string to date/string in different timezone
- 28
How to use INDIRECT with a SUMIF formula for a date range
- 29
Unable to use the same formula for different rows in excel
Comments