SumIF referencing to a CELL, rather than text....

[rai_lanc]

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

 

[Bob Phillips]

=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)

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