I have a formula that works in Excel 2007 but not Excel 2003. The formula
needs to have two conditions satisfied before the entry in a third cell is
added into a cell in another work sheet: all within the same Workbook. The
working Excel 2007 formula is below:

SUMIFS('2003 StarWords.xlsx'!GrandTotal,'2003
StarWords.xlsx'!WeekOf,"1/13",'2003 StarWords.xlsx'!Name,"Abby")

I'm having to change this formula as the formula is using the SUMIFS
Function but the school I'm working at is using Excel 2003 consequently I
need to rewrite the formula.

Any direction would be most appreciated.

SW

Is "1/13" a true Excel date or a text string? SUMIFS evaluates it as *both*
and the replacement function you need evaluates it as a text string.

Here's the general syntax you need (I'm leaving out the file names so be
sure to add them in your formula):

=SUMPRODUCT(--(WeekOf="1/13"),--(Name="abby"),GrandTotal)

In the above, "1/13" is a TEXT string. If "1/13" is really a DATE then use
this syntax:

=SUMPRODUCT(--(WeekOf=DATE(2009,1,13)),--(Name="abby"),GrandTotal)

Also note that in previous versions of Excel you *can't* use entire columns
as range references with SUMPRODUCT. So, if your named ranges are entire
columns you'll have to use a smaller more specific range.


--
Biff
Microsoft Excel MVP


"SW" wrote in message
...
I have a formula that works in Excel 2007 but not Excel 2003. The formula
needs to have two conditions satisfied before the entry in a third cell is
added into a cell in another work sheet: all within the same Workbook.
The
working Excel 2007 formula is below:

SUMIFS('2003 StarWords.xlsx'!GrandTotal,'2003
StarWords.xlsx'!WeekOf,"1/13",'2003 StarWords.xlsx'!Name,"Abby")

I'm having to change this formula as the formula is using the SUMIFS
Function but the school I'm working at is using Excel 2003 consequently I
need to rewrite the formula.

Any direction would be most appreciated.

SW

Thank you so much. You have been instramental in my being able to share a
tracking tool with the entire elementary school. I am much appreciative.

Sincrely, Steve W
--
SW


"T. Valko" wrote:

Is "1/13" a true Excel date or a text string? SUMIFS evaluates it as *both*
and the replacement function you need evaluates it as a text string.

Here's the general syntax you need (I'm leaving out the file names so be
sure to add them in your formula):

=SUMPRODUCT(--(WeekOf="1/13"),--(Name="abby"),GrandTotal)

In the above, "1/13" is a TEXT string. If "1/13" is really a DATE then use
this syntax:

=SUMPRODUCT(--(WeekOf=DATE(2009,1,13)),--(Name="abby"),GrandTotal)

Also note that in previous versions of Excel you *can't* use entire columns
as range references with SUMPRODUCT. So, if your named ranges are entire
columns you'll have to use a smaller more specific range.


--
Biff
Microsoft Excel MVP


"SW" wrote in message
...
I have a formula that works in Excel 2007 but not Excel 2003. The formula
needs to have two conditions satisfied before the entry in a third cell is
added into a cell in another work sheet: all within the same Workbook.
The
working Excel 2007 formula is below:

SUMIFS('2003 StarWords.xlsx'!GrandTotal,'2003
StarWords.xlsx'!WeekOf,"1/13",'2003 StarWords.xlsx'!Name,"Abby")

I'm having to change this formula as the formula is using the SUMIFS
Function but the school I'm working at is using Excel 2003 consequently I
need to rewrite the formula.

Any direction would be most appreciated.

SW

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"SW" wrote in message
...
Thank you so much. You have been instramental in my being able to share a
tracking tool with the entire elementary school. I am much appreciative.

Sincrely, Steve W
--
SW


"T. Valko" wrote:

Is "1/13" a true Excel date or a text string? SUMIFS evaluates it as
*both*
and the replacement function you need evaluates it as a text string.

Here's the general syntax you need (I'm leaving out the file names so be
sure to add them in your formula):

=SUMPRODUCT(--(WeekOf="1/13"),--(Name="abby"),GrandTotal)

In the above, "1/13" is a TEXT string. If "1/13" is really a DATE then
use
this syntax:

=SUMPRODUCT(--(WeekOf=DATE(2009,1,13)),--(Name="abby"),GrandTotal)

Also note that in previous versions of Excel you *can't* use entire
columns
as range references with SUMPRODUCT. So, if your named ranges are entire
columns you'll have to use a smaller more specific range.


--
Biff
Microsoft Excel MVP


"SW" wrote in message
...
I have a formula that works in Excel 2007 but not Excel 2003. The
formula
needs to have two conditions satisfied before the entry in a third cell
is
added into a cell in another work sheet: all within the same Workbook.
The
working Excel 2007 formula is below:

SUMIFS('2003 StarWords.xlsx'!GrandTotal,'2003
StarWords.xlsx'!WeekOf,"1/13",'2003 StarWords.xlsx'!Name,"Abby")

I'm having to change this formula as the formula is using the SUMIFS
Function but the school I'm working at is using Excel 2003 consequently
I
need to rewrite the formula.

Any direction would be most appreciated.

SW