Need Function/Formula that will add a column based on two conditon
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
|