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

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

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

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

=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

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

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

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

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

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

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

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

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

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

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

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

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

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

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