SUM using Three ranges. Two Comparisons and the SUM Range
 

I have a problem creating a function like this:

MySUMIF(range1, condition2range1, range2,
condition2range2, sumRange) As Double

Because I have data like this:

A B C
1 Tree Owner Profit
2 Apple Shawn 5.325611365
3 Pear Edward 8.325687876
4 Cherry John 25.69556655
5 Apple John 2.565678955
6 Apple John 63.59654647
7 Pear Edward 5.996465456
8 Pear Shawn 3.567898


but i need to call this function on another sheet, example

=MySUMIF(Sheet1!$A$2:$A$8, "Apple", Sheet1! B$2:$B$8,
"John", Sheet1!C$2:C$8)

I know this function is really easy but how can I use the
three ranges at the same time to get the sum and make the
respective comparisons.

I try using three diferent "for" statements but It makes
my algorithm O(n)^3, what I need - I think - is how to use
the three ranges in one "for" statement


For Each rng,rgn2,rgn3 in myRange1, myRange2, myRange3
....
Next rng

I have been tring to use the Offset property but I seems
no work to me.


If anyone knows how can I have every value on the
respective range.
PLEASE HELPME.


Thanks In Advace.

czamora.

SUM using Three ranges. Two Comparisons and the SUM Range
 

Thanks a Lot :)

-----Original Message-----
But take the comma out after "A$8" and the right
parenthesis after "B$8".

Alan Beban

Bob Phillips wrote:
Why not use the SUMPRODUCT worksheet function
=SUMPRODUCT((Sheet1!$A$2:$A$8,="Apple")*( Sheet1!
B$2:$B$8)="John"),(Sheet1!C$2:C$8))

--

HTH

Bob Phillips

"czamora" wrote in message
...

I have a problem creating a function like this:

MySUMIF(range1, condition2range1, range2,
condition2range2, sumRange) As Double

Because I have data like this:

A B C
1 Tree Owner Profit
2 Apple Shawn 5.325611365
3 Pear Edward 8.325687876
4 Cherry John 25.69556655
5 Apple John 2.565678955
6 Apple John 63.59654647
7 Pear Edward 5.996465456
8 Pear Shawn 3.567898


but i need to call this function on another sheet,
example

=MySUMIF(Sheet1!$A$2:$A$8, "Apple", Sheet1! B$2:$B$8,
"John", Sheet1!C$2:C$8)

I know this function is really easy but how can I use
the
three ranges at the same time to get the sum and make
the
respective comparisons.

I try using three diferent "for" statements but It makes
my algorithm O(n)^3, what I need - I think - is how to
use
the three ranges in one "for" statement


For Each rng,rgn2,rgn3 in myRange1, myRange2, myRange3
....
Next rng

I have been tring to use the Offset property but I seems
no work to me.


If anyone knows how can I have every value on the
respective range.
PLEASE HELPME.


Thanks In Advace.

czamora.






.