Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumIF referencing to a CELL, rather than text....
OK, this is getting on my nerves now, and i'm sure i'm just missing
something really simple!! I have 3 columns of data | A || B || C | | Date || No. Vouchers || Value | | 01/09/07 || 1 || £30.00 | | 01/09/07 || 1 || £40.00 | | 01/09/07 || 1 || £50.00 | | 04/09/07 || 1 || £20.00 | | 04/09/07 || 1 || £15.00 | | 08/09/07 || 1 || £30.00 | | 08/09/07 || 1 || £33.00 | | 08/09/07 || 1 || £32.00 | and my data goes down a few rows (about 80 at the moment) I need to sort this data into weeks, and i have a separate sheet set- up with the following formula: =SUMIFS(B:B,A:A,"01/09/07",A:A,"<08/09/07") This works fine for me. It's telling me the total number of vouchers used between my dates set in the query. In this case, it's 5 vouchers. However, this is the annoying part!!! My other spreadsheet has a singular column of data with the dates for week/ending: | A | B | 1 01/09/07 | | 2 08/09/07 | | 3 15/09/07 | | 4 22/09/07 | | etc... What I want to do (and in theory it sounds really simple...) is: =SUMIFS(Sheet1!$B:$B,Sheet1$A:$A,"A1",Sheet1!$A:$ A,"<A2") Here, i've referenced to the original columns of data in my first sheet, but rather than putting the dates in my criteria, i've got reference to cells... However, it tells me that there were NO vouchers using that query, but I KNOW that there were!!! So, "01/09/07" works, whereas "A1" doesn't!! Please help, what on earth am I doing wrong?? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumIF referencing to a CELL, rather than text....
=SUMIFS(Sheet1!$B:$B,Sheet1$A:$A,""&A1,Sheet1!$A: $A,"<A2")
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message oups.com... OK, this is getting on my nerves now, and i'm sure i'm just missing something really simple!! I have 3 columns of data | A || B || C | | Date || No. Vouchers || Value | | 01/09/07 || 1 || £30.00 | | 01/09/07 || 1 || £40.00 | | 01/09/07 || 1 || £50.00 | | 04/09/07 || 1 || £20.00 | | 04/09/07 || 1 || £15.00 | | 08/09/07 || 1 || £30.00 | | 08/09/07 || 1 || £33.00 | | 08/09/07 || 1 || £32.00 | and my data goes down a few rows (about 80 at the moment) I need to sort this data into weeks, and i have a separate sheet set- up with the following formula: =SUMIFS(B:B,A:A,"01/09/07",A:A,"<08/09/07") This works fine for me. It's telling me the total number of vouchers used between my dates set in the query. In this case, it's 5 vouchers. However, this is the annoying part!!! My other spreadsheet has a singular column of data with the dates for week/ending: | A | B | 1 01/09/07 | | 2 08/09/07 | | 3 15/09/07 | | 4 22/09/07 | | etc... What I want to do (and in theory it sounds really simple...) is: =SUMIFS(Sheet1!$B:$B,Sheet1$A:$A,"A1",Sheet1!$A:$ A,"<A2") Here, i've referenced to the original columns of data in my first sheet, but rather than putting the dates in my criteria, i've got reference to cells... However, it tells me that there were NO vouchers using that query, but I KNOW that there were!!! So, "01/09/07" works, whereas "A1" doesn't!! Please help, what on earth am I doing wrong?? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
chart Label text referencing worksheet cell value | Charts and Charting in Excel | |||
Sumif text is contained winthin a longer text string in a cell | Excel Worksheet Functions | |||
Referencing cell text in a lookup | Excel Discussion (Misc queries) | |||
Referencing text in a cell in a formula | Excel Discussion (Misc queries) | |||
Referencing a formula (as text) | Excel Discussion (Misc queries) |