|
Convert SUMIFS formula from 2007 to 2003
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 |
Convert SUMIFS formula from 2007 to 2003
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 |
Convert SUMIFS formula from 2007 to 2003
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 |
Convert SUMIFS formula from 2007 to 2003
=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 |
SUMIFS
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 |
SUMIFS
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) |
SUMIFS
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 |
Convert SUMIFS formula from 2007 to 2003
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. |
Convert SUMIFS formula from 2007 to 2003
=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. |
Convert SUMIFS formula from 2007 to 2003
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 |
Convert SUMIFS formula from 2007 to 2003
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 |
Convert SUMIFS formula from 2007 to 2003
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 |
Convert SUMIFS formula from 2007 to 2003
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 |
Convert SUMIFS formula from 2007 to 2003
#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 |
Convert SUMIFS formula from 2007 to 2003
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 |
Convert SUMIFS formula from 2007 to 2003
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 |
Convert SUMIFS formula from 2007 to 2003
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 |
SUMIFS
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 |
Convert SUMIFS formula from 2007 to 2003
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))) |
Convert SUMIFS formula from 2007 to 2003
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))) |
Convert SUMIFS formula from 2007 to 2003
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! |
Convert SUMIFS formula from 2007 to 2003
=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! |
Convert SUMIFS formula from 2007 to 2003
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! |
Convert SUMIFS formula from 2007 to 2003
=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! |
Convert SUMIFS formula from 2007 to 2003
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! |
Convert SUMIFS formula from 2007 to 2003
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! |
| All times are GMT +1. The time now is 04:00 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com