SUMIF and <
 

Hi

I have this equation which works well to SUM the values in two sheets if the
tax rate applied in the same row is the same as that in this sheet's cell E10.

=SUM(SUMIF('Sheet1'!$G$9:$G$152,"="&E10,'Sheet1'!$ H$9:$H$152))+(SUMIF('Sheet2'!$G$9:$G$29,"="&E10,'S heet2'!$H$9:$H$29))

I have a similar equation to calculate a further two values (in cells E11
and E12), as I currently have three different tax rates.

However, if purchases are made overseas using tax rates that I cannot forsee
I need another equation to calculate the values in the two sheets if the tax
rate applied in the same row is NOT the same as that in this sheet's cell
E10, E11 or E12.

Anybody any thoughts on how to approach this?

Thanks in advance, a Taxed Mind.

SUMIF and <
 

=SUMPRODUCT(--(NOT(ISNUMBER(MATCH(Sheet1!$G$9:$G$152,E10:E12,0)) )),Sheet1!$H$9:$H$152)+SUMPRODUCT(--(NOT(ISNUMBER(MATCH(Sheet2!$G$9:$G$152,E10:E12,0)) )),Sheet2!$H$9:$H$152)

BTW, your formula can be simplified

=SUMIF(Sheet1!$G$9:$G$152,E10,Sheet1!$H$9:$H$152)+ SUMIF(Sheet2!$G$9:$G$29,E10,Sheet2!$H$9:$H$29)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Taxed Mind" wrote in message
...
Hi

I have this equation which works well to SUM the values in two sheets if
the
tax rate applied in the same row is the same as that in this sheet's cell
E10.

=SUM(SUMIF('Sheet1'!$G$9:$G$152,"="&E10,'Sheet1'!$ H$9:$H$152))+(SUMIF('Sheet2'!$G$9:$G$29,"="&E10,'S heet2'!$H$9:$H$29))

I have a similar equation to calculate a further two values (in cells E11
and E12), as I currently have three different tax rates.

However, if purchases are made overseas using tax rates that I cannot
forsee
I need another equation to calculate the values in the two sheets if the
tax
rate applied in the same row is NOT the same as that in this sheet's cell
E10, E11 or E12.

Anybody any thoughts on how to approach this?

Thanks in advance, a Taxed Mind.

SUMIF and <
 

Hi Bob thanks for your grand equation and your simplification of the other,
it is very much appreciated. I have some quick questions though and the
Excel help features can only go so far in explaining; I think I am I correct
in understanding that for your equation:

=SUMPRODUCT(--(NOT(ISNUMBER(MATCH(Sheet1!$G$9:$G$152,E10:E12,0)) )),Sheet1!$H$9:$H$152)+SUMPRODUCT(--(NOT(ISNUMBER(MATCH(Sheet2!$G$9:$G$152,E10:E12,0)) )),Sheet2!$H$9:$H$152)

MATCH determines all the values the same as cells E10:E12
ISNUMBER checks to ensure that all the returned vales are numbers and not
text, etc. (Is this unnecessary if we have faith in the quality of the
original data?).
NOT reverses the info so that we now have every other value from those
originally MATCHed.
-- I don't recognise this at all. Is it a calculation operator (like two
minuses = plus) or is it a worksheet function?
SUMPRODUCT adds the values in column H which belong to the values in column
G which we have just determined are not the same as those values in cells
E10:E12

Thanks again for your help, I am very grateful. If there are two of these
posts I apologise, the first disappeared while posting.

"Bob Phillips" wrote:

=SUMPRODUCT(--(NOT(ISNUMBER(MATCH(Sheet1!$G$9:$G$152,E10:E12,0)) )),Sheet1!$H$9:$H$152)+SUMPRODUCT(--(NOT(ISNUMBER(MATCH(Sheet2!$G$9:$G$152,E10:E12,0)) )),Sheet2!$H$9:$H$152)

BTW, your formula can be simplified

=SUMIF(Sheet1!$G$9:$G$152,E10,Sheet1!$H$9:$H$152)+ SUMIF(Sheet2!$G$9:$G$29,E10,Sheet2!$H$9:$H$29)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Taxed Mind" wrote in message
...
Hi

I have this equation which works well to SUM the values in two sheets if
the
tax rate applied in the same row is the same as that in this sheet's cell
E10.

=SUM(SUMIF('Sheet1'!$G$9:$G$152,"="&E10,'Sheet1'!$ H$9:$H$152))+(SUMIF('Sheet2'!$G$9:$G$29,"="&E10,'S heet2'!$H$9:$H$29))

I have a similar equation to calculate a further two values (in cells E11
and E12), as I currently have three different tax rates.

However, if purchases are made overseas using tax rates that I cannot
forsee
I need another equation to calculate the values in the two sheets if the
tax
rate applied in the same row is NOT the same as that in this sheet's cell
E10, E11 or E12.

Anybody any thoughts on how to approach this?

Thanks in advance, a Taxed Mind.

SUMIF and <
 

"Taxed Mind" wrote in message
...
Hi Bob thanks for your grand equation and your simplification of the
other,
it is very much appreciated.


It's a pleasure


I have some quick questions though and the
Excel help features can only go so far in explaining; I think I am I
correct
in understanding that for your equation:

=SUMPRODUCT(--(NOT(ISNUMBER(MATCH(Sheet1!$G$9:$G$152,E10:E12,0)) )),Sheet1!$H$9:$H$152)+SUMPRODUCT(--(NOT(ISNUMBER(MATCH(Sheet2!$G$9:$G$152,E10:E12,0)) )),Sheet2!$H$9:$H$152)

MATCH determines all the values the same as cells E10:E12


No, it checks whether range G9:G152 holds any of the values in E10:E12


ISNUMBER checks to ensure that all the returned vales are numbers and not
text, etc. (Is this unnecessary if we have faith in the quality of the
original data?).


Quality of the data is immaterial. MATCH returns an array of numbers, if the
range is in E10:E12, or #N/A, if not. IS NUMBER then returns an array of
TRUE (matched) or FALSE (not matched).


NOT reverses the info so that we now have every other value from those
originally MATCHed.


This just changes TRUE/FALSE to FALSE/TRUE because you want to find those
not matching the codes in E10:E12


-- I don't recognise this at all. Is it a calculation operator (like two
minuses = plus) or is it a worksheet function?


It is exactly that, two minuses, or a double unary as it is often referred
to.

The first minus changes that array of FALSE/TRUE to an array of 0/-1, the
secondchanges it to an array of 0/1. This array is used to multiply (the
PRODUCT in SUMPRODUCT) by the array of values, H9:H152.


SUMPRODUCT adds the values in column H which belong to the values in
column
G which we have just determined are not the same as those values in cells
E10:E12


The SUM part of SUMPRODUCT then SUMs the results of all of thos PRODUCT
calculations.

See http://xldynamic.com/source/xld.SUMPRODUCT.html for a more complete
explanation.

SUMIF and <
 

Thanks for your help in developing a greater understanding of this.


"Bob Phillips" wrote:


"Taxed Mind" wrote in message
...
Hi Bob thanks for your grand equation and your simplification of the
other,
it is very much appreciated.


It's a pleasure


I have some quick questions though and the
Excel help features can only go so far in explaining; I think I am I
correct
in understanding that for your equation:

=SUMPRODUCT(--(NOT(ISNUMBER(MATCH(Sheet1!$G$9:$G$152,E10:E12,0)) )),Sheet1!$H$9:$H$152)+SUMPRODUCT(--(NOT(ISNUMBER(MATCH(Sheet2!$G$9:$G$152,E10:E12,0)) )),Sheet2!$H$9:$H$152)

MATCH determines all the values the same as cells E10:E12


No, it checks whether range G9:G152 holds any of the values in E10:E12


ISNUMBER checks to ensure that all the returned vales are numbers and not
text, etc. (Is this unnecessary if we have faith in the quality of the
original data?).


Quality of the data is immaterial. MATCH returns an array of numbers, if the
range is in E10:E12, or #N/A, if not. IS NUMBER then returns an array of
TRUE (matched) or FALSE (not matched).


NOT reverses the info so that we now have every other value from those
originally MATCHed.


This just changes TRUE/FALSE to FALSE/TRUE because you want to find those
not matching the codes in E10:E12


-- I don't recognise this at all. Is it a calculation operator (like two
minuses = plus) or is it a worksheet function?


It is exactly that, two minuses, or a double unary as it is often referred
to.

The first minus changes that array of FALSE/TRUE to an array of 0/-1, the
secondchanges it to an array of 0/1. This array is used to multiply (the
PRODUCT in SUMPRODUCT) by the array of values, H9:H152.


SUMPRODUCT adds the values in column H which belong to the values in
column
G which we have just determined are not the same as those values in cells
E10:E12


The SUM part of SUMPRODUCT then SUMs the results of all of thos PRODUCT
calculations.

See http://xldynamic.com/source/xld.SUMPRODUCT.html for a more complete
explanation.

SUMIF and <
 

Thanks, Bob, for your help in developing my understanding of this great
programme.

A Taxed Mind

"Bob Phillips" wrote:


"Taxed Mind" wrote in message
...
Hi Bob thanks for your grand equation and your simplification of the
other,
it is very much appreciated.


It's a pleasure


I have some quick questions though and the
Excel help features can only go so far in explaining; I think I am I
correct
in understanding that for your equation:

=SUMPRODUCT(--(NOT(ISNUMBER(MATCH(Sheet1!$G$9:$G$152,E10:E12,0)) )),Sheet1!$H$9:$H$152)+SUMPRODUCT(--(NOT(ISNUMBER(MATCH(Sheet2!$G$9:$G$152,E10:E12,0)) )),Sheet2!$H$9:$H$152)

MATCH determines all the values the same as cells E10:E12


No, it checks whether range G9:G152 holds any of the values in E10:E12


ISNUMBER checks to ensure that all the returned vales are numbers and not
text, etc. (Is this unnecessary if we have faith in the quality of the
original data?).


Quality of the data is immaterial. MATCH returns an array of numbers, if the
range is in E10:E12, or #N/A, if not. IS NUMBER then returns an array of
TRUE (matched) or FALSE (not matched).


NOT reverses the info so that we now have every other value from those
originally MATCHed.


This just changes TRUE/FALSE to FALSE/TRUE because you want to find those
not matching the codes in E10:E12


-- I don't recognise this at all. Is it a calculation operator (like two
minuses = plus) or is it a worksheet function?


It is exactly that, two minuses, or a double unary as it is often referred
to.

The first minus changes that array of FALSE/TRUE to an array of 0/-1, the
secondchanges it to an array of 0/1. This array is used to multiply (the
PRODUCT in SUMPRODUCT) by the array of values, H9:H152.


SUMPRODUCT adds the values in column H which belong to the values in
column
G which we have just determined are not the same as those values in cells
E10:E12


The SUM part of SUMPRODUCT then SUMs the results of all of thos PRODUCT
calculations.

See http://xldynamic.com/source/xld.SUMPRODUCT.html for a more complete
explanation.