|
SUMIF(S) not available on Excel 2003
I have created a worksheet at home on Excel 2007 using 'sumifs' and
'countifs' formulae and when I have saved this (in .xls format) and then opened it in Excel 2003 in work, it doesn't recognise this formula. Is there a way of converting this appropriately or an alternative formula I can use for sum/count if command with multiple criteria Thanks G |
SUMIF(S) not available on Excel 2003
SUMPRODUCT is likely to be the best bet.
-- David Biddulph "Longhag" wrote in message ... I have created a worksheet at home on Excel 2007 using 'sumifs' and 'countifs' formulae and when I have saved this (in .xls format) and then opened it in Excel 2003 in work, it doesn't recognise this formula. Is there a way of converting this appropriately or an alternative formula I can use for sum/count if command with multiple criteria Thanks G |
SUMIF(S) not available on Excel 2003
As you have found, they are new to 2007. Try using sumproduct((etc. or the array sum(if( etc. Post your formula(s) if necessary. -- Don Guillett Microsoft MVP Excel SalesAid Software "Longhag" wrote in message ... I have created a worksheet at home on Excel 2007 using 'sumifs' and 'countifs' formulae and when I have saved this (in .xls format) and then opened it in Excel 2003 in work, it doesn't recognise this formula. Is there a way of converting this appropriately or an alternative formula I can use for sum/count if command with multiple criteria Thanks G |
SUMIF(S) not available on Excel 2003
David,
Thanks for your response. Could you explain a little further how this one will work? Cheers G "David Biddulph" wrote: SUMPRODUCT is likely to be the best bet. -- David Biddulph "Longhag" wrote in message ... I have created a worksheet at home on Excel 2007 using 'sumifs' and 'countifs' formulae and when I have saved this (in .xls format) and then opened it in Excel 2003 in work, it doesn't recognise this formula. Is there a way of converting this appropriately or an alternative formula I can use for sum/count if command with multiple criteria Thanks G |
SUMIF(S) not available on Excel 2003
=SUMPRODUCT(--(Range1=value1),--(Range2="value2"),Range2Sum)
-- __________________________________ HTH Bob "Longhag" wrote in message ... David, Thanks for your response. Could you explain a little further how this one will work? Cheers G "David Biddulph" wrote: SUMPRODUCT is likely to be the best bet. -- David Biddulph "Longhag" wrote in message ... I have created a worksheet at home on Excel 2007 using 'sumifs' and 'countifs' formulae and when I have saved this (in .xls format) and then opened it in Excel 2003 in work, it doesn't recognise this formula. Is there a way of converting this appropriately or an alternative formula I can use for sum/count if command with multiple criteria Thanks G |
SUMIF(S) not available on Excel 2003
This is my Excel 2007 formula - how would I convert this into 2003:
=SUMIFS(I4:I302,A4:A302,1,G4:G302,"written") '1' being the criteria in the first range, 'written' being the criteria in the second range and I4:I302 is the sum range. Thanks for your help "Don Guillett" wrote: As you have found, they are new to 2007. Try using sumproduct((etc. or the array sum(if( etc. Post your formula(s) if necessary. -- Don Guillett Microsoft MVP Excel SalesAid Software "Longhag" wrote in message ... I have created a worksheet at home on Excel 2007 using 'sumifs' and 'countifs' formulae and when I have saved this (in .xls format) and then opened it in Excel 2003 in work, it doesn't recognise this formula. Is there a way of converting this appropriately or an alternative formula I can use for sum/count if command with multiple criteria Thanks G |
SUMIF(S) not available on Excel 2003
This is my Excel 2007 formula - how would I convert this into 2003:
=SUMIFS(I4:I302,A4:A302,1,G4:G302,"written") '1' being the criteria in the first range, 'written' being the criteria in the second range and I4:I302 is the sum range. Thanks for your help "David Biddulph" wrote: SUMPRODUCT is likely to be the best bet. -- David Biddulph "Longhag" wrote in message ... I have created a worksheet at home on Excel 2007 using 'sumifs' and 'countifs' formulae and when I have saved this (in .xls format) and then opened it in Excel 2003 in work, it doesn't recognise this formula. Is there a way of converting this appropriately or an alternative formula I can use for sum/count if command with multiple criteria Thanks G |
SUMIF(S) not available on Excel 2003
This is so close. Where you have value1 my value is the number 1 and it is
saying there is an error. This is my Excel 2007 formula - how would I convert this into 2003: =SUMIFS(I4:I302,A4:A302,1,G4:G302,"written") '1' being the criteria in the first range, 'written' being the criteria in the second range and I4:I302 is the sum range. Thanks for your help "Bob Phillips" wrote: =SUMPRODUCT(--(Range1=value1),--(Range2="value2"),Range2Sum) -- __________________________________ HTH Bob "Longhag" wrote in message ... David, Thanks for your response. Could you explain a little further how this one will work? Cheers G "David Biddulph" wrote: SUMPRODUCT is likely to be the best bet. -- David Biddulph "Longhag" wrote in message ... I have created a worksheet at home on Excel 2007 using 'sumifs' and 'countifs' formulae and when I have saved this (in .xls format) and then opened it in Excel 2003 in work, it doesn't recognise this formula. Is there a way of converting this appropriately or an alternative formula I can use for sum/count if command with multiple criteria Thanks G |
SUMIF(S) not available on Excel 2003
Don,
The second one seems to be the closest but it doesn't like the =1 in the first criteria. Should this be written any other way? "Don Guillett" wrote: try =sumproduct((a4:a302=1)*(g4:g302="written)*i4:i302 ) or sumproduct(--(a4:a302=1),--(g4:g302="written"),i4:i302) -- Don Guillett Microsoft MVP Excel SalesAid Software "Longhag" wrote in message ... This is my Excel 2007 formula - how would I convert this into 2003: =SUMIFS(I4:I302,A4:A302,1,G4:G302,"written") '1' being the criteria in the first range, 'written' being the criteria in the second range and I4:I302 is the sum range. Thanks for your help "Don Guillett" wrote: As you have found, they are new to 2007. Try using sumproduct((etc. or the array sum(if( etc. Post your formula(s) if necessary. -- Don Guillett Microsoft MVP Excel SalesAid Software "Longhag" wrote in message ... I have created a worksheet at home on Excel 2007 using 'sumifs' and 'countifs' formulae and when I have saved this (in .xls format) and then opened it in Excel 2003 in work, it doesn't recognise this formula. Is there a way of converting this appropriately or an alternative formula I can use for sum/count if command with multiple criteria Thanks G |
SUMIF(S) not available on Excel 2003
That would just be
=SUMPRODUCT(--(A4:A302=1),--(G4:G302="written"),I4:I302) or =SUMPRODUCT(--(A4:A302="1"),--(G4:G302="written"),I4:I302) if not real numbers -- __________________________________ HTH Bob "Longhag" wrote in message ... This is so close. Where you have value1 my value is the number 1 and it is saying there is an error. This is my Excel 2007 formula - how would I convert this into 2003: =SUMIFS(I4:I302,A4:A302,1,G4:G302,"written") '1' being the criteria in the first range, 'written' being the criteria in the second range and I4:I302 is the sum range. Thanks for your help "Bob Phillips" wrote: =SUMPRODUCT(--(Range1=value1),--(Range2="value2"),Range2Sum) -- __________________________________ HTH Bob "Longhag" wrote in message ... David, Thanks for your response. Could you explain a little further how this one will work? Cheers G "David Biddulph" wrote: SUMPRODUCT is likely to be the best bet. -- David Biddulph "Longhag" wrote in message ... I have created a worksheet at home on Excel 2007 using 'sumifs' and 'countifs' formulae and when I have saved this (in .xls format) and then opened it in Excel 2003 in work, it doesn't recognise this formula. Is there a way of converting this appropriately or an alternative formula I can use for sum/count if command with multiple criteria Thanks G |
SUMIF(S) not available on Excel 2003
It's always useful if you tell us what formula you are using when you get an
error, and what error you are getting. Were you using =SUMPRODUCT(--(A4:A302=1),--(G4:G302="written"),I4:I302) ? If so, what error did Excel tell you there was? Another way of writing it would be =SUMPRODUCT((A4:A302=1)*(G4:G302="written")*(I4:I3 02)) -- David Biddulph "Longhag" wrote in message ... This is so close. Where you have value1 my value is the number 1 and it is saying there is an error. This is my Excel 2007 formula - how would I convert this into 2003: =SUMIFS(I4:I302,A4:A302,1,G4:G302,"written") '1' being the criteria in the first range, 'written' being the criteria in the second range and I4:I302 is the sum range. Thanks for your help "Bob Phillips" wrote: =SUMPRODUCT(--(Range1=value1),--(Range2="value2"),Range2Sum) -- __________________________________ HTH Bob "Longhag" wrote in message ... David, Thanks for your response. Could you explain a little further how this one will work? Cheers G "David Biddulph" wrote: SUMPRODUCT is likely to be the best bet. -- David Biddulph "Longhag" wrote in message ... I have created a worksheet at home on Excel 2007 using 'sumifs' and 'countifs' formulae and when I have saved this (in .xls format) and then opened it in Excel 2003 in work, it doesn't recognise this formula. Is there a way of converting this appropriately or an alternative formula I can use for sum/count if command with multiple criteria Thanks G |
SUMIF(S) not available on Excel 2003
wher you have written -- do I insert if ;also it doesn't like 1 or "1"
Sorry to keep bothering you "Bob Phillips" wrote: That would just be =SUMPRODUCT(--(A4:A302=1),--(G4:G302="written"),I4:I302) or =SUMPRODUCT(--(A4:A302="1"),--(G4:G302="written"),I4:I302) if not real numbers -- __________________________________ HTH Bob "Longhag" wrote in message ... This is so close. Where you have value1 my value is the number 1 and it is saying there is an error. This is my Excel 2007 formula - how would I convert this into 2003: =SUMIFS(I4:I302,A4:A302,1,G4:G302,"written") '1' being the criteria in the first range, 'written' being the criteria in the second range and I4:I302 is the sum range. Thanks for your help "Bob Phillips" wrote: =SUMPRODUCT(--(Range1=value1),--(Range2="value2"),Range2Sum) -- __________________________________ HTH Bob "Longhag" wrote in message ... David, Thanks for your response. Could you explain a little further how this one will work? Cheers G "David Biddulph" wrote: SUMPRODUCT is likely to be the best bet. -- David Biddulph "Longhag" wrote in message ... I have created a worksheet at home on Excel 2007 using 'sumifs' and 'countifs' formulae and when I have saved this (in .xls format) and then opened it in Excel 2003 in work, it doesn't recognise this formula. Is there a way of converting this appropriately or an alternative formula I can use for sum/count if command with multiple criteria Thanks G |
SUMIF(S) not available on Excel 2003
The second one works. Is there a similar solution for countifs ?
This is my current 2007 formula : =COUNTIFS(A4:A302,2,G4:G302,"written") Regards G "David Biddulph" wrote: It's always useful if you tell us what formula you are using when you get an error, and what error you are getting. Were you using =SUMPRODUCT(--(A4:A302=1),--(G4:G302="written"),I4:I302) ? If so, what error did Excel tell you there was? Another way of writing it would be =SUMPRODUCT((A4:A302=1)*(G4:G302="written")*(I4:I3 02)) -- David Biddulph "Longhag" wrote in message ... This is so close. Where you have value1 my value is the number 1 and it is saying there is an error. This is my Excel 2007 formula - how would I convert this into 2003: =SUMIFS(I4:I302,A4:A302,1,G4:G302,"written") '1' being the criteria in the first range, 'written' being the criteria in the second range and I4:I302 is the sum range. Thanks for your help "Bob Phillips" wrote: =SUMPRODUCT(--(Range1=value1),--(Range2="value2"),Range2Sum) -- __________________________________ HTH Bob "Longhag" wrote in message ... David, Thanks for your response. Could you explain a little further how this one will work? Cheers G "David Biddulph" wrote: SUMPRODUCT is likely to be the best bet. -- David Biddulph "Longhag" wrote in message ... I have created a worksheet at home on Excel 2007 using 'sumifs' and 'countifs' formulae and when I have saved this (in .xls format) and then opened it in Excel 2003 in work, it doesn't recognise this formula. Is there a way of converting this appropriately or an alternative formula I can use for sum/count if command with multiple criteria Thanks G |
SUMIF(S) not available on Excel 2003
No, that works as given, assuming you don't use ; as a separator.
-- __________________________________ HTH Bob "Longhag" wrote in message ... wher you have written -- do I insert if ;also it doesn't like 1 or "1" Sorry to keep bothering you "Bob Phillips" wrote: That would just be =SUMPRODUCT(--(A4:A302=1),--(G4:G302="written"),I4:I302) or =SUMPRODUCT(--(A4:A302="1"),--(G4:G302="written"),I4:I302) if not real numbers -- __________________________________ HTH Bob "Longhag" wrote in message ... This is so close. Where you have value1 my value is the number 1 and it is saying there is an error. This is my Excel 2007 formula - how would I convert this into 2003: =SUMIFS(I4:I302,A4:A302,1,G4:G302,"written") '1' being the criteria in the first range, 'written' being the criteria in the second range and I4:I302 is the sum range. Thanks for your help "Bob Phillips" wrote: =SUMPRODUCT(--(Range1=value1),--(Range2="value2"),Range2Sum) -- __________________________________ HTH Bob "Longhag" wrote in message ... David, Thanks for your response. Could you explain a little further how this one will work? Cheers G "David Biddulph" wrote: SUMPRODUCT is likely to be the best bet. -- David Biddulph "Longhag" wrote in message ... I have created a worksheet at home on Excel 2007 using 'sumifs' and 'countifs' formulae and when I have saved this (in .xls format) and then opened it in Excel 2003 in work, it doesn't recognise this formula. Is there a way of converting this appropriately or an alternative formula I can use for sum/count if command with multiple criteria Thanks G |
SUMIF(S) not available on Excel 2003
The firsst one works. Is there a similar solution for countifs ?
This is my current 2007 formula : =COUNTIFS(A4:A302,2,G4:G302,"written") Regards G "Don Guillett" wrote: try =sumproduct((a4:a302=1)*(g4:g302="written)*i4:i302 ) or sumproduct(--(a4:a302=1),--(g4:g302="written"),i4:i302) -- Don Guillett Microsoft MVP Excel SalesAid Software "Longhag" wrote in message ... This is my Excel 2007 formula - how would I convert this into 2003: =SUMIFS(I4:I302,A4:A302,1,G4:G302,"written") '1' being the criteria in the first range, 'written' being the criteria in the second range and I4:I302 is the sum range. Thanks for your help "Don Guillett" wrote: As you have found, they are new to 2007. Try using sumproduct((etc. or the array sum(if( etc. Post your formula(s) if necessary. -- Don Guillett Microsoft MVP Excel SalesAid Software "Longhag" wrote in message ... I have created a worksheet at home on Excel 2007 using 'sumifs' and 'countifs' formulae and when I have saved this (in .xls format) and then opened it in Excel 2003 in work, it doesn't recognise this formula. Is there a way of converting this appropriately or an alternative formula I can use for sum/count if command with multiple criteria Thanks G |
SUMIF(S) not available on Excel 2003
Just delete the parameter that summed based on criteria
=sumproduct((a4:a302=1)*(g4:g302="written)) -- Don Guillett Microsoft MVP Excel SalesAid Software "Longhag" wrote in message ... The firsst one works. Is there a similar solution for countifs ? This is my current 2007 formula : =COUNTIFS(A4:A302,2,G4:G302,"written") Regards G "Don Guillett" wrote: try =sumproduct((a4:a302=1)*(g4:g302="written)*i4:i302 ) or sumproduct(--(a4:a302=1),--(g4:g302="written"),i4:i302) -- Don Guillett Microsoft MVP Excel SalesAid Software "Longhag" wrote in message ... This is my Excel 2007 formula - how would I convert this into 2003: =SUMIFS(I4:I302,A4:A302,1,G4:G302,"written") '1' being the criteria in the first range, 'written' being the criteria in the second range and I4:I302 is the sum range. Thanks for your help "Don Guillett" wrote: As you have found, they are new to 2007. Try using sumproduct((etc. or the array sum(if( etc. Post your formula(s) if necessary. -- Don Guillett Microsoft MVP Excel SalesAid Software "Longhag" wrote in message ... I have created a worksheet at home on Excel 2007 using 'sumifs' and 'countifs' formulae and when I have saved this (in .xls format) and then opened it in Excel 2003 in work, it doesn't recognise this formula. Is there a way of converting this appropriately or an alternative formula I can use for sum/count if command with multiple criteria Thanks G |
SUMIF(S) not available on Excel 2003
Thanks for all your help today. All looks to be working well.
Regards Gareth "Don Guillett" wrote: Just delete the parameter that summed based on criteria =sumproduct((a4:a302=1)*(g4:g302="written)) -- Don Guillett Microsoft MVP Excel SalesAid Software "Longhag" wrote in message ... The firsst one works. Is there a similar solution for countifs ? This is my current 2007 formula : =COUNTIFS(A4:A302,2,G4:G302,"written") Regards G "Don Guillett" wrote: try =sumproduct((a4:a302=1)*(g4:g302="written)*i4:i302 ) or sumproduct(--(a4:a302=1),--(g4:g302="written"),i4:i302) -- Don Guillett Microsoft MVP Excel SalesAid Software "Longhag" wrote in message ... This is my Excel 2007 formula - how would I convert this into 2003: =SUMIFS(I4:I302,A4:A302,1,G4:G302,"written") '1' being the criteria in the first range, 'written' being the criteria in the second range and I4:I302 is the sum range. Thanks for your help "Don Guillett" wrote: As you have found, they are new to 2007. Try using sumproduct((etc. or the array sum(if( etc. Post your formula(s) if necessary. -- Don Guillett Microsoft MVP Excel SalesAid Software "Longhag" wrote in message ... I have created a worksheet at home on Excel 2007 using 'sumifs' and 'countifs' formulae and when I have saved this (in .xls format) and then opened it in Excel 2003 in work, it doesn't recognise this formula. Is there a way of converting this appropriately or an alternative formula I can use for sum/count if command with multiple criteria Thanks G |
SUMIF(S) not available on Excel 2003
So isn't it as simple as either
=SUMPRODUCT(--(A4:A302=1),--(G4:G302="written")) or =SUMPRODUCT((A4:A302=1)*(G4:G302="written")) ? -- David Biddulph "Longhag" wrote in message ... The second one works. Is there a similar solution for countifs ? This is my current 2007 formula : =COUNTIFS(A4:A302,2,G4:G302,"written") Regards G "David Biddulph" wrote: It's always useful if you tell us what formula you are using when you get an error, and what error you are getting. Were you using =SUMPRODUCT(--(A4:A302=1),--(G4:G302="written"),I4:I302) ? If so, what error did Excel tell you there was? Another way of writing it would be =SUMPRODUCT((A4:A302=1)*(G4:G302="written")*(I4:I3 02)) -- David Biddulph "Longhag" wrote in message ... This is so close. Where you have value1 my value is the number 1 and it is saying there is an error. This is my Excel 2007 formula - how would I convert this into 2003: =SUMIFS(I4:I302,A4:A302,1,G4:G302,"written") '1' being the criteria in the first range, 'written' being the criteria in the second range and I4:I302 is the sum range. Thanks for your help "Bob Phillips" wrote: =SUMPRODUCT(--(Range1=value1),--(Range2="value2"),Range2Sum) -- __________________________________ HTH Bob "Longhag" wrote in message ... David, Thanks for your response. Could you explain a little further how this one will work? Cheers G "David Biddulph" wrote: SUMPRODUCT is likely to be the best bet. -- David Biddulph "Longhag" wrote in message ... I have created a worksheet at home on Excel 2007 using 'sumifs' and 'countifs' formulae and when I have saved this (in .xls format) and then opened it in Excel 2003 in work, it doesn't recognise this formula. Is there a way of converting this appropriately or an alternative formula I can use for sum/count if command with multiple criteria Thanks G |
| All times are GMT +1. The time now is 04:15 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com