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?? |
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?? |
All times are GMT +1. The time now is 10:19 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com