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

--
Biff
Microsoft Excel MVP


"Alex.W" wrote in message
...
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

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

"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(Sheet1!B6:B100="incident"),--(Sheet1!T6:T100=B4))+SUMPRODUCT(--(Sheet2!B6:B100="incident"),--(Sheet2!T6:T100=B4))

--
Biff
Microsoft Excel MVP


"Alex.W" wrote in message
...
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

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

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:B10010),--(Sheet1!B6:B100<=20)

--
Biff
Microsoft Excel MVP


"Alex.W" wrote in message
...
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

"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(Sheet1!B6:B100="incident"),--(Sheet1!T6:T100=B4))+SUMPRODUCT(--(Sheet2!B6:B100="incident"),--(Sheet2!T6:T100=B4))

--
Biff
Microsoft Excel MVP


"Alex.W" wrote in message
...
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

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

I would like to use the next formula in excel2003.
=SUMIFS(E$5:E$33;D$5:D$33;"TXNS1***")

Maybe what you're looking for is
=SUMIF(E$5:E$33,"TXNS1***",D$5:D$33)

In SUMIFS (Excel 2007 only) the sytax is
=SUMIFS(range_to_sum, )
and the last two arguments can be repeated many times

The syntax for SUMIF is
=SUMIF(range_to_test, criteria, range_to_sum)

best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme



"attila nemet" wrote in message
...
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))

--
__________________________________
HTH

Bob

"Funso" wrote in message
...
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.

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




"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(Sheet1!B6:B100="incident"),--(Sheet1!T6:T100=B4))+SUMPRODUCT(--(Sheet2!B6:B100="incident"),--(Sheet2!T6:T100=B4))

--
Biff
Microsoft Excel MVP


"Alex.W" wrote in message
...
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

In the COUNTIFS version you're using B2 but in the SUMPRODUCT version
you're using <B2.



--
Biff
Microsoft Excel MVP


"Corrine" wrote in message
...
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




"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(Sheet1!B6:B100="incident"),--(Sheet1!T6:T100=B4))+SUMPRODUCT(--(Sheet2!B6:B100="incident"),--(Sheet2!T6:T100=B4))

--
Biff
Microsoft Excel MVP


"Alex.W" wrote in message
...
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

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$289B2))

Any ideas?

Thanks for checking it, Corrine

"T. Valko" wrote:

In the COUNTIFS version you're using B2 but in the SUMPRODUCT version
you're using <B2.



--
Biff
Microsoft Excel MVP


"Corrine" wrote in message
...
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




"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(Sheet1!B6:B100="incident"),--(Sheet1!T6:T100=B4))+SUMPRODUCT(--(Sheet2!B6:B100="incident"),--(Sheet2!T6:T100=B4))

--
Biff
Microsoft Excel MVP


"Alex.W" wrote in message
...
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

Define in more detail: "doesn't work".

Incorrect result? An error? A result of 0 when there should be a greater
number?

--
Biff
Microsoft Excel MVP


"Corrine" wrote in message
...
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$289B2))

Any ideas?

Thanks for checking it, Corrine

"T. Valko" wrote:

In the COUNTIFS version you're using B2 but in the SUMPRODUCT version
you're using <B2.



--
Biff
Microsoft Excel MVP


"Corrine" wrote in message
...
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




"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(Sheet1!B6:B100="incident"),--(Sheet1!T6:T100=B4))+SUMPRODUCT(--(Sheet2!B6:B100="incident"),--(Sheet2!T6:T100=B4))

--
Biff
Microsoft Excel MVP


"Alex.W" wrote in message
...
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

#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

"T. Valko" wrote:

Define in more detail: "doesn't work".

Incorrect result? An error? A result of 0 when there should be a greater
number?

--
Biff
Microsoft Excel MVP


"Corrine" wrote in message
...
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$289B2))

Any ideas?

Thanks for checking it, Corrine

"T. Valko" wrote:

In the COUNTIFS version you're using B2 but in the SUMPRODUCT version
you're using <B2.



--
Biff
Microsoft Excel MVP


"Corrine" wrote in message
...
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




"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(Sheet1!B6:B100="incident"),--(Sheet1!T6:T100=B4))+SUMPRODUCT(--(Sheet2!B6:B100="incident"),--(Sheet2!T6:T100=B4))

--
Biff
Microsoft Excel MVP


"Alex.W" wrote in message
...
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

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.

--
Biff
Microsoft Excel MVP


"Corrine" wrote in message
...
#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

"T. Valko" wrote:

Define in more detail: "doesn't work".

Incorrect result? An error? A result of 0 when there should be a greater
number?

--
Biff
Microsoft Excel MVP


"Corrine" wrote in message
...
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$289B2))

Any ideas?

Thanks for checking it, Corrine

"T. Valko" wrote:

In the COUNTIFS version you're using B2 but in the SUMPRODUCT version
you're using <B2.



--
Biff
Microsoft Excel MVP


"Corrine" wrote in message
...
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




"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(Sheet1!B6:B100="incident"),--(Sheet1!T6:T100=B4))+SUMPRODUCT(--(Sheet2!B6:B100="incident"),--(Sheet2!T6:T100=B4))

--
Biff
Microsoft Excel MVP


"Alex.W" wrote in message
...
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

Great! I fixed the #N/A errors and then all worked fine.

thank you, Corrine

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"Corrine" wrote in message
...
#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

"T. Valko" wrote:

Define in more detail: "doesn't work".

Incorrect result? An error? A result of 0 when there should be a greater
number?

--
Biff
Microsoft Excel MVP


"Corrine" wrote in message
...
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$289B2))

Any ideas?

Thanks for checking it, Corrine

"T. Valko" wrote:

In the COUNTIFS version you're using B2 but in the SUMPRODUCT version
you're using <B2.



--
Biff
Microsoft Excel MVP


"Corrine" wrote in message
...
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




"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(Sheet1!B6:B100="incident"),--(Sheet1!T6:T100=B4))+SUMPRODUCT(--(Sheet2!B6:B100="incident"),--(Sheet2!T6:T100=B4))

--
Biff
Microsoft Excel MVP


"Alex.W" wrote in message
...
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

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Corrine" wrote in message
...
Great! I fixed the #N/A errors and then all worked fine.

thank you, Corrine

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"Corrine" wrote in message
...
#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

"T. Valko" wrote:

Define in more detail: "doesn't work".

Incorrect result? An error? A result of 0 when there should be a
greater
number?

--
Biff
Microsoft Excel MVP


"Corrine" wrote in message
...
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$289B2))

Any ideas?

Thanks for checking it, Corrine

"T. Valko" wrote:

In the COUNTIFS version you're using B2 but in the SUMPRODUCT
version
you're using <B2.



--
Biff
Microsoft Excel MVP


"Corrine" wrote in message
...
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




"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(Sheet1!B6:B100="incident"),--(Sheet1!T6:T100=B4))+SUMPRODUCT(--(Sheet2!B6:B100="incident"),--(Sheet2!T6:T100=B4))

--
Biff
Microsoft Excel MVP


"Alex.W" wrote in message
...
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

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



"attila nemet" wrote:

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

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$2 002;$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)))

--
Regards
Roger Govier

"Trufox" wrote in message
...
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$2 002;$A4;Geral!$F$3:$F$2002;1)))

Hello, I have one here that I can't convert as well:

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

Any clue?

Thanks in advance!

=SUMPRODUCT(N(Sheet1!A2:A11=Sheet2!$A2),N(NOT(ISER ROR(FIND(Sheet2!B1,Sheet1!C2:C11)))),Sheet1!D2:D11 )

"Fellipe C. Moreira" <Fellipe C. wrote in
message ...
Hello, I have one here that I can't convert as well:

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

Any clue?

Thanks in advance!

Thanks for the quick response Bob!

However I couldn't make it work, it's giving me 0 when it should give me
other value as the other does, any idea?

"Bob Umlas" wrote:

=SUMPRODUCT(N(Sheet1!A2:A11=Sheet2!$A2),N(NOT(ISER ROR(FIND(Sheet2!B1,Sheet1!C2:C11)))),Sheet1!D2:D11 )

"Fellipe C. Moreira" <Fellipe C. wrote in
message ...
Hello, I have one here that I can't convert as well:

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

Any clue?

Thanks in advance!

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

Try this:

=SUMPRODUCT(--(Sheet1!A2:A11=Sheet2!A2),--(ISNUMBER(SEARCH(Sheet2!B1,Sheet1!C2:C11))),Sheet1 !D2:D11)

--
Biff
Microsoft Excel MVP


"Fellipe C. Moreira" wrote in
message ...
Thanks for the quick response Bob!

However I couldn't make it work, it's giving me 0 when it should give me
other value as the other does, any idea?

"Bob Umlas" wrote:

=SUMPRODUCT(N(Sheet1!A2:A11=Sheet2!$A2),N(NOT(ISER ROR(FIND(Sheet2!B1,Sheet1!C2:C11)))),Sheet1!D2:D11 )

"Fellipe C. Moreira" <Fellipe C. wrote
in
message ...
Hello, I have one here that I can't convert as well:

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

Any clue?

Thanks in advance!

This one worked, thanks a lot!

"T. Valko" wrote:

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

Try this:

=SUMPRODUCT(--(Sheet1!A2:A11=Sheet2!A2),--(ISNUMBER(SEARCH(Sheet2!B1,Sheet1!C2:C11))),Sheet1 !D2:D11)

--
Biff
Microsoft Excel MVP


"Fellipe C. Moreira" wrote in
message ...
Thanks for the quick response Bob!

However I couldn't make it work, it's giving me 0 when it should give me
other value as the other does, any idea?

"Bob Umlas" wrote:

=SUMPRODUCT(N(Sheet1!A2:A11=Sheet2!$A2),N(NOT(ISER ROR(FIND(Sheet2!B1,Sheet1!C2:C11)))),Sheet1!D2:D11 )

"Fellipe C. Moreira" <Fellipe C. wrote
in
message ...
Hello, I have one here that I can't convert as well:

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

Any clue?

Thanks in advance!

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Fellipe C. Moreira" wrote in
message ...
This one worked, thanks a lot!

"T. Valko" wrote:

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

Try this:

=SUMPRODUCT(--(Sheet1!A2:A11=Sheet2!A2),--(ISNUMBER(SEARCH(Sheet2!B1,Sheet1!C2:C11))),Sheet1 !D2:D11)

--
Biff
Microsoft Excel MVP


"Fellipe C. Moreira" wrote in
message ...
Thanks for the quick response Bob!

However I couldn't make it work, it's giving me 0 when it should give
me
other value as the other does, any idea?

"Bob Umlas" wrote:

=SUMPRODUCT(N(Sheet1!A2:A11=Sheet2!$A2),N(NOT(ISER ROR(FIND(Sheet2!B1,Sheet1!C2:C11)))),Sheet1!D2:D11 )

"Fellipe C. Moreira" <Fellipe C.
wrote
in
message ...
Hello, I have one here that I can't convert as well:

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

Any clue?

Thanks in advance!