A
Andy
Hi,
SUMPRODUCT question:
I hope I haven't muddied a simple problem by my explanation, but here goes...
Using the excellent SUMPRODUCT resource at
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
what I am after is similar to Table 1, where there is a make month and price
columns, only that I would also want to sum other columns as well, such as
(for example) State tax, US tax / whatever (in my real example I'm trying to
sum up columns each holding one months accounting data, but I only have one
criteria rather than having Make and Month on the example).
I also have the criteria held in a range to allow for multiple values (e.g.
Ford and BWM)
(I hope you're following OK so far)
So, if I have a separate range called CARS, which stores FORD and BWM, I can
get the total price for fords and BMW’s with the following..
=SUMPRODUCT(--(ISNUMBER(MATCH(A2:A10,CARS,0))),C2:C10)
* - the criteria range
If I then add another column 'TAX' in column D, how do I get the total of
both columns (C and D) rather than just C?
Entering the following formula gets a #Value! error
=SUMPRODUCT(--(ISNUMBER(MATCH(A2:A10,CARS,0))),C2
10)
* - changed C2:C10 to C210
We're in a mixed environment of Excel97 and 2002 if this makes any difference.
As ever your help is most appreciated.
Kind Regards,
Andy
Sunny Pembrokeshire, UK (ok I lie, its actually chucking it down)
SUMPRODUCT question:
I hope I haven't muddied a simple problem by my explanation, but here goes...
Using the excellent SUMPRODUCT resource at
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
what I am after is similar to Table 1, where there is a make month and price
columns, only that I would also want to sum other columns as well, such as
(for example) State tax, US tax / whatever (in my real example I'm trying to
sum up columns each holding one months accounting data, but I only have one
criteria rather than having Make and Month on the example).
I also have the criteria held in a range to allow for multiple values (e.g.
Ford and BWM)
(I hope you're following OK so far)
So, if I have a separate range called CARS, which stores FORD and BWM, I can
get the total price for fords and BMW’s with the following..
=SUMPRODUCT(--(ISNUMBER(MATCH(A2:A10,CARS,0))),C2:C10)
* - the criteria range
If I then add another column 'TAX' in column D, how do I get the total of
both columns (C and D) rather than just C?
Entering the following formula gets a #Value! error
=SUMPRODUCT(--(ISNUMBER(MATCH(A2:A10,CARS,0))),C2
10)* - changed C2:C10 to C2
10We're in a mixed environment of Excel97 and 2002 if this makes any difference.
As ever your help is most appreciated.
Kind Regards,
Andy
Sunny Pembrokeshire, UK (ok I lie, its actually chucking it down)