Convert SUMIFS formula from 2007 to 2003

  • Thread starter Thread starter Alex.W
  • Start date Start date
A

Alex.W

I have this formula that works in Excel 2007. I need to get it to work in
Excel 2003. Can anyone help?

SUM(COUNTIFS('Sheet1'!B6:B100,"incident",'Sheet1'!T6:T100,B4)+COUNTIFS('Sheet2'!B6:B100,"incident",'Sheet2'!T6:T100,B4))

Alex.W
 
Try this:

=SUMPRODUCT(--(Sheet1!B6:B100="incident"),--(Sheet1!T6:T100=B4))+SUMPRODUCT(--(Sheet2!B6:B100="incident"),--(Sheet2!T6:T100=B4))
 
Many thanks it works well.

It did however highlight another problem you might be able to assist with.
Using the same formula (Sheet1!B6:B100) how do I get it to return entries
that are in a range? For example >10 but <=20.

Alex.W
 
=SUMPRODUCT(--(Sheet1!B6:B100="incident"),--(Sheet1!T6:T100=B4))+SUMPRODUCT(--(Sheet2!B6:B100="incident"),--(Sheet2!T6:T100=B4))
Using the same formula (Sheet1!B6:B100) how do I get it to return entries
that are in a range? For example >10 but <=20.
Click to expand...

Well, "using the same formula" won't work. You're already testing B6:B100 to
see if it equals "incident". That range can't = incident *and* be >10 but
<=20 at the same time!

Add 2 arrays:

--(Sheet1!B6:B100>10),--(Sheet1!B6:B100<=20)
 
Hi,

I would like to use the next formula in excel2003.

Please tell me if you know any solution.


=SUMIFS(E$5:E$33;D$5:D$33;"TXNS1***")

Thank you

Attila
 
Please can you help me with this formula too. The SUMIFS section doesn't seem
to be working.

=IF($D$1="All",SUMIFS('Master Sheet'!$W$20:$W$1028,'Master
Sheet'!$L$20:$L$1028,$D$2,'Master Sheet'!$D$20:$D$1028,$A4,'Master
Sheet'!$O$20:$O$1028,B$3,'Master Sheet'!$W$20:$W$1028,$G$2),SUMIFS('Master
Sheet'!$W$20:$W$1028,'Master Sheet'!$J$20:$J$1028,$D$1,'Master
Sheet'!$L$20:$L$1028,$D$2,'Master Sheet'!$D$20:$D$1028,$A4,'Master
Sheet'!$O$20:$O$1028,B$3,'Master Sheet'!$W$20:$W$1028,$G$2))


I would appreciate it if anyone could help me convert it to an Excel 2003
formula. This is very urgent.

Thanks.
 
=IF($D$1="All",SUMPRODUCT(
--('Master Sheet'!$L$20:$L$1028=$D$2),--('Master Sheet'!$D$20:$D$1028=$A4),
--('Master Sheet'!$O$20:$O$1028=B$3),--('Master
Sheet'!$W$20:$W$1028=$G$2),'Master Sheet'!$W$20:$W$1028),
SUMPRODUCT(
--('Master Sheet'!$J$20:$J$1028=$D$1),--('Master Sheet'!$L$20:$L$1028=$D$2),
--('Master Sheet'!$D$20:$D$1028=$A4),--('Master Sheet'!$O$20:$O$1028=B$3),
--('Master Sheet'!$W$20:$W$1028=$G$2),'Master Sheet'!$W$20:$W$1028))
 
This is the formula I have in 2007 ...
=COUNTIFS(Submittal!$A$1:$A$288,">"&b2,Submittal!$A$1:$A$288,"<"&b3,
Submittal!$J$1:$J$288, "=Success")
Column B holds a number for different weeks

I tried this but no luck...
=SUMPRODUCT(--(Submittal!$J$1:$J$288="Success"),--(Submittal!$A$1:$A$288<B3),--(Submittal!$A$1:$A$288<B2))

Any help would be appreciated to point out my errors. Thanks, Corrine
 
In the COUNTIFS version you're using >B2 but in the SUMPRODUCT version
you're using <B2.
 
Yes - I typed it wrong - changed it and it still doesn't work.
=SUMPRODUCT(--(Submittal!$J$1:$J$289="Success"),--(Submittal!$A$1:$A$289<B3),--(Submittal!$A$1:$A$289>B2))

Any ideas?

Thanks for checking it, Corrine
 
Define in more detail: "doesn't work".

Incorrect result? An error? A result of 0 when there should be a greater
number?
 
#N/A is the result. If I leave off the first part
(--SUMPRODUCT(--(Submittal!$J$1:$J$289="Success"), it gives the answer up to
that point.

Thanks for your help, Corrine
 
Do you have #N/A errors in any of the referenced ranges? That'll cause the
result to be #N/A.

If the #N/A errors are in a numeric range you'll have to fix those. If the
#N/A errors are in a text range you can work around those but it's *really*
complicated and would be much easier to just fix those as well.
 
Hi Attila

It's been some time, but I just recently had the same problem. Since I
didn't want to change the formula that I got from a Excel 2007 Version, I
created with VBA a custom formula "SUMIFS". It's not as good as the Excel
2007 version, but it's a start and maybe others can find it useful.

It basically takes the same syntax: SUMIFS(Range to sum, CriteriaRange1,
Criteria 1, CriteriaRange2, Criteria 2, CriteriaRange3, Criteria 3)

It seems to work for me, but haven't tested it to the max. Simply copy and
paste the code into your workbook (Alt-F11 opens VB)



'******************************************************************************************
'************* SumIf-Function as in XL-2007
**********************************************
'******************************************************************************************
Function SumIfs(SumRng As Range, Crit1Rng As Range, Criteria1 As String,
Crit2Rng As Range, Criteria2 As String, Crit3Rng As Range, Criteria3 As
String) As Double
'The following parameters are necessary
'SumRng as Range: Range to be summed up
'Crit1Rng as Range: Range where the first criteria is
'Criteria1 As String: String with the criteria for Crit1Rng
'Two more pairs with Range and Criteria
'Returns a value as double

Dim c As Range, ColI As Integer, cnt As Integer, C1Cols As Integer,
C2Cols As Integer, C3Cols As Integer
Dim C1 As Boolean, C2 As Boolean, C3 As Boolean, Is1Date As Boolean,
Is2Date As Boolean, Is3Date As Boolean
Dim C1RVal As Variant, C2RVal As Variant, C3RVal As Variant

cnt = 1
C1 = False
C2 = False
C3 = False
Is1Date = False
Is2Date = False
Is3Date = False
ColI = SumRng.Column + 1

For Each c In SumRng
C1Cols = ColI - c.Column
C2Cols = ColI - c.Column
C3Cols = ColI - c.Column

'Criteria1
C1RVal = Crit1Rng.Value(cnt, C1Cols)
If VarType(C1RVal) = 7 Then
Is1Date = True
End If
Select Case True
Case InStr(Left(Criteria1, 2), "<=") > 0
If Is1Date Then
If C1RVal <= Int(Right(Criteria1, Len(Criteria1) - 2)) Then
C1 = True
End If
Else
If C1RVal <= Right(Criteria1, Len(Criteria1) - 2) Then
C1 = True
End If
End If
Case InStr(Left(Criteria1, 2), ">=") > 0
If Is1Date Then
If C1RVal >= Int(Right(Criteria1, Len(Criteria1) - 2)) Then
C1 = True
End If
Else
If C1RVal >= Right(Criteria1, Len(Criteria1) - 2) Then
C1 = True
End If
End If
Case InStr(Left(Criteria1, 2), "<>") > 0
If Is1Date Then
If C1RVal <> Int(Right(Criteria1, Len(Criteria1) - 2)) Then
C1 = True
End If
Else
If C1RVal <> Right(Criteria1, Len(Criteria1) - 2) Then
C1 = True
End If
End If
Case InStr(Left(Criteria1, 1), "<") > 0
If Is1Date Then
If C1RVal < Int(Right(Criteria1, Len(Criteria1) - 1)) Then
C1 = True
End If
Else
If C1RVal < Right(Criteria1, Len(Criteria1) - 1) Then
C1 = True
End If
End If
Case InStr(Left(Criteria1, 1), ">") > 0
If Is1Date Then
If C1RVal > Int(Right(Criteria1, Len(Criteria1) - 1)) Then
C1 = True
End If
Else
If C1RVal > Right(Criteria1, Len(Criteria1) - 1) Then
C1 = True
End If
End If
Case InStr(Left(Criteria1, 1), "=") > 0
If Is1Date Then
If C1RVal = Int(Right(Criteria1, Len(Criteria1) - 1)) Then
C1 = True
End If
Else
If C1RVal = Right(Criteria1, Len(Criteria1) - 1) Then
C1 = True
End If
End If
Case Else
If C1RVal = Criteria1 Then
C1 = True
End If
End Select


'Criteria2
C2RVal = Crit2Rng.Value(cnt, C2Cols)
If VarType(C2RVal) = 7 Then
Is2Date = True
End If
Select Case True
Case InStr(Left(Criteria2, 2), "<=") > 0
If Is2Date Then
If C2RVal <= Int(Right(Criteria2, Len(Criteria2) - 2)) Then
C2 = True
End If
Else
If C2RVal <= Right(Criteria2, Len(Criteria2) - 2) Then
C2 = True
End If
End If
Case InStr(Left(Criteria2, 2), ">=") > 0
If Is2Date Then
If C2RVal >= Int(Right(Criteria2, Len(Criteria2) - 2)) Then
C2 = True
End If
Else
If C2RVal >= Right(Criteria2, Len(Criteria2) - 2) Then
C2 = True
End If
End If
Case InStr(Left(Criteria2, 2), "<>") > 0
If Is2Date Then
If C2RVal <> Int(Right(Criteria2, Len(Criteria2) - 2)) Then
C2 = True
End If
Else
If C2RVal <> Right(Criteria2, Len(Criteria2) - 2) Then
C2 = True
End If
End If
Case InStr(Left(Criteria2, 1), "<") > 0
If Is2Date Then
If C2RVal < Int(Right(Criteria2, Len(Criteria2) - 1)) Then
C2 = True
End If
Else
If C2RVal < Right(Criteria2, Len(Criteria2) - 1) Then
C2 = True
End If
End If
Case InStr(Left(Criteria2, 1), ">") > 0
If Is2Date Then
If C2RVal > Int(Right(Criteria2, Len(Criteria2) - 1)) Then
C2 = True
End If
Else
If C2RVal > Right(Criteria2, Len(Criteria2) - 1) Then
C2 = True
End If
End If
Case InStr(Left(Criteria2, 1), "=") > 0
If Is2Date Then
If C2RVal = Int(Right(Criteria2, Len(Criteria2) - 1)) Then
C2 = True
End If
Else
If C2RVal = Right(Criteria2, Len(Criteria2) - 1) Then
C2 = True
End If
End If
Case Else
If C2RVal = Criteria2 Then
C2 = True
End If
End Select
Is2Date = False

'Criteria3
C3RVal = Crit3Rng.Value(cnt, C3Cols)
If VarType(C3RVal) = 7 Then
Is3Date = True
End If
Select Case True
Case InStr(Left(Criteria3, 2), "<=") > 0
If Is3Date Then
If C3RVal <= Int(Right(Criteria3, Len(Criteria3) - 2)) Then
C3 = True
End If
Else
If C3RVal <= Right(Criteria3, Len(Criteria3) - 2) Then
C3 = True
End If
End If
Case InStr(Left(Criteria3, 2), ">=") > 0
If Is3Date Then
If C3RVal >= Int(Right(Criteria3, Len(Criteria3) - 2)) Then
C3 = True
End If
Else
If C3RVal >= Right(Criteria3, Len(Criteria3) - 2) Then
C3 = True
End If
End If
Case InStr(Left(Criteria3, 2), "<>") > 0
If Is3Date Then
If C3RVal <> Int(Right(Criteria3, Len(Criteria3) - 2)) Then
C3 = True
End If
Else
If C3RVal <> Right(Criteria3, Len(Criteria3) - 2) Then
C3 = True
End If
End If
Case InStr(Left(Criteria3, 1), "<") > 0
If Is3Date Then
If C3RVal < Int(Right(Criteria3, Len(Criteria3) - 1)) Then
C3 = True
End If
Else
If C3RVal < Right(Criteria3, Len(Criteria3) - 1) Then
C3 = True
End If
End If
Case InStr(Left(Criteria3, 1), ">") > 0
If Is3Date Then
If C3RVal > Int(Right(Criteria3, Len(Criteria3) - 1)) Then
C3 = True
End If
Else
If C3RVal > Right(Criteria3, Len(Criteria3) - 1) Then
C3 = True
End If
End If
Case InStr(Left(Criteria3, 1), "=") > 0
If Is3Date Then
If C3RVal = Int(Right(Criteria3, Len(Criteria3) - 1)) Then
C3 = True
End If
Else
If C3RVal = Right(Criteria3, Len(Criteria3) - 1) Then
C3 = True
End If
End If
Case Else
If C3RVal = Criteria3 Then
C3 = True
End If
End Select
Is3Date = False

If C1 = True And C2 = True And C3 = True Then 'If Crit1, Crit2 and
Crit3 are true, then sum the cell
SumIfs = SumIfs + c.Value
End If
C1 = False
C2 = False
C3 = False

cnt = cnt + 1
Next

End Function
 
Someone can help me to convert this 2007 formula to 2003? Thanks!

=SE(Participantes!A3="";"";SE(SUMIFS(Geral!$G$3:$G$2002;Geral!$B$3:$B$2002;$A4;Geral!$F$3:$F$2002;1)<=0;$A$1;SUMIFS(Geral!$G$3:$G$2002;Geral!$B$3:$B$2002;$A4;Geral!$F$3:$F$2002;1)))
 
Hi

Try
=SE(Participantes!A3="";"";
SE(SUMPRODUCT(--(Geral!$B$3:$B$2002=$A4);--(Geral!$F$3:$F$2002=1);Geral!$G$3:$G$2002)<=0;$A$1;
SUMPRODUCT(--(Geral!$B$3:$B$2002=$A4);--(Geral!$F$3:$F$2002=1);Geral!$G$3:$G$2002)))
 
Hello, I have one here that I can't convert as well:

=SUMIFS(Sheet1!D2:D11,Sheet1!A2:A11,Sheet2!$A2,Sheet1!C2:C11,"*"&Sheet2!B$1&"*")

Any clue?

Thanks in advance!
 
Back
Top