sumproduct with multiple variables
 

hi everyone,

here is my issue. i am using SUMPRODUCT for a couple of criteria and
returning the sum of another column. E.g.

A B 1
A C 2
B C 3
B E 4
C A 5
C D 5

=SUMPRODUCT((A1:A6="A")*(B1:B6="B"),C1:C6)+SUMPROD UCT((A1:A6="C")*(B1:B6="D"),C1:C6)
= 1 + 5
= 6

This all works fine but I was wondering if there was another option so i
would not have to use SUMPRODUCT multiple times. It is essential that A is
matched with B and that C is matched with D.

Thanks

Sam

sumproduct with multiple variables
 

Based on your sample data this portion returns 0:

=SUMPRODUCT((A1:A6="A")*(B1:B6="B"),C1:C6)

And this portion returns 5:

SUMPRODUCT((A1:A6="C")*(B1:B6="D"),C1:C6)

Combined, the result is 5.

So, I have no idea what this means:

= 1 + 5
= 6

--
Biff
Microsoft Excel MVP


"sam" wrote in message
...
hi everyone,

here is my issue. i am using SUMPRODUCT for a couple of criteria and
returning the sum of another column. E.g.

A B 1
A C 2
B C 3
B E 4
C A 5
C D 5

=SUMPRODUCT((A1:A6="A")*(B1:B6="B"),C1:C6)+SUMPROD UCT((A1:A6="C")*(B1:B6="D"),C1:C6)
= 1 + 5
= 6

This all works fine but I was wondering if there was another option so i
would not have to use SUMPRODUCT multiple times. It is essential that A is
matched with B and that C is matched with D.

Thanks

Sam

sumproduct with multiple variables
 

Try the below

=SUMPRODUCT(ISNUMBER(MATCH(A1:A6,{"A","C"},0))*
ISNUMBER(MATCH(B1:B6,{"B","D"},0)),C1:C6)

If this post helps click Yes
---------------
Jacob Skaria


"sam" wrote:

hi everyone,

here is my issue. i am using SUMPRODUCT for a couple of criteria and
returning the sum of another column. E.g.

A B 1
A C 2
B C 3
B E 4
C A 5
C D 5

=SUMPRODUCT((A1:A6="A")*(B1:B6="B"),C1:C6)+SUMPROD UCT((A1:A6="C")*(B1:B6="D"),C1:C6)
= 1 + 5
= 6

This all works fine but I was wondering if there was another option so i
would not have to use SUMPRODUCT multiple times. It is essential that A is
matched with B and that C is matched with D.

Thanks

Sam

sumproduct with multiple variables
 

That doesn't meet the criteria:

It is essential that A is matched with B and
that C is matched with D.

--
Biff
Microsoft Excel MVP


"Jacob Skaria" wrote in message
...
Try the below

=SUMPRODUCT(ISNUMBER(MATCH(A1:A6,{"A","C"},0))*
ISNUMBER(MATCH(B1:B6,{"B","D"},0)),C1:C6)

If this post helps click Yes
---------------
Jacob Skaria


"sam" wrote:

hi everyone,

here is my issue. i am using SUMPRODUCT for a couple of criteria and
returning the sum of another column. E.g.

A B 1
A C 2
B C 3
B E 4
C A 5
C D 5

=SUMPRODUCT((A1:A6="A")*(B1:B6="B"),C1:C6)+SUMPROD UCT((A1:A6="C")*(B1:B6="D"),C1:C6)
= 1 + 5
= 6

This all works fine but I was wondering if there was another option so i
would not have to use SUMPRODUCT multiple times. It is essential that A
is
matched with B and that C is matched with D.

Thanks

Sam

sumproduct with multiple variables
 

Doesn't row 1 return 1 (not 0) from first formula, Biff?
--
David Biddulph

"T. Valko" wrote in message
...
Based on your sample data this portion returns 0:

=SUMPRODUCT((A1:A6="A")*(B1:B6="B"),C1:C6)

And this portion returns 5:

SUMPRODUCT((A1:A6="C")*(B1:B6="D"),C1:C6)

Combined, the result is 5.

So, I have no idea what this means:

= 1 + 5
= 6

--
Biff
Microsoft Excel MVP


"sam" wrote in message
...
hi everyone,

here is my issue. i am using SUMPRODUCT for a couple of criteria and
returning the sum of another column. E.g.

A B 1
A C 2
B C 3
B E 4
C A 5
C D 5

=SUMPRODUCT((A1:A6="A")*(B1:B6="B"),C1:C6)+SUMPROD UCT((A1:A6="C")*(B1:B6="D"),C1:C6)
= 1 + 5
= 6

This all works fine but I was wondering if there was another option so i
would not have to use SUMPRODUCT multiple times. It is essential that A
is
matched with B and that C is matched with D.

Thanks

Sam

sumproduct with multiple variables
 

=SUMPRODUCT((A1:A6&B1:B6="AB")+(A1:A6&B1:B6="CD"), C1:C6)
(assuming that each column has only a single character and thus that you
can't get AB or CD in one column and the other blank).
--
David Biddulph

"sam" wrote in message
...
hi everyone,

here is my issue. i am using SUMPRODUCT for a couple of criteria and
returning the sum of another column. E.g.

A B 1
A C 2
B C 3
B E 4
C A 5
C D 5

=SUMPRODUCT((A1:A6="A")*(B1:B6="B"),C1:C6)+SUMPROD UCT((A1:A6="C")*(B1:B6="D"),C1:C6)
= 1 + 5
= 6

This all works fine but I was wondering if there was another option so i
would not have to use SUMPRODUCT multiple times. It is essential that A is
matched with B and that C is matched with D.

Thanks

Sam

sumproduct with multiple variables
 

Ooops!

Yes it does. When I copied/pasted the data into a file I somehow missed row
1. My row 1 was:

A C 2

--
Biff
Microsoft Excel MVP


"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
Doesn't row 1 return 1 (not 0) from first formula, Biff?
--
David Biddulph

"T. Valko" wrote in message
...
Based on your sample data this portion returns 0:

=SUMPRODUCT((A1:A6="A")*(B1:B6="B"),C1:C6)

And this portion returns 5:

SUMPRODUCT((A1:A6="C")*(B1:B6="D"),C1:C6)

Combined, the result is 5.

So, I have no idea what this means:

= 1 + 5
= 6

--
Biff
Microsoft Excel MVP


"sam" wrote in message
...
hi everyone,

here is my issue. i am using SUMPRODUCT for a couple of criteria and
returning the sum of another column. E.g.

A B 1
A C 2
B C 3
B E 4
C A 5
C D 5

=SUMPRODUCT((A1:A6="A")*(B1:B6="B"),C1:C6)+SUMPROD UCT((A1:A6="C")*(B1:B6="D"),C1:C6)
= 1 + 5
= 6

This all works fine but I was wondering if there was another option so i
would not have to use SUMPRODUCT multiple times. It is essential that A
is
matched with B and that C is matched with D.

Thanks

Sam

sumproduct with multiple variables
 

You can also use

=SUMPRODUCT((A1:A6&B1:B6={"AB","CD"})*C1:C6)

--
__________________________________
HTH

Bob

"sam" wrote in message
...
hi everyone,

here is my issue. i am using SUMPRODUCT for a couple of criteria and
returning the sum of another column. E.g.

A B 1
A C 2
B C 3
B E 4
C A 5
C D 5

=SUMPRODUCT((A1:A6="A")*(B1:B6="B"),C1:C6)+SUMPROD UCT((A1:A6="C")*(B1:B6="D"),C1:C6)
= 1 + 5
= 6

This all works fine but I was wondering if there was another option so i
would not have to use SUMPRODUCT multiple times. It is essential that A is
matched with B and that C is matched with D.

Thanks

Sam

sumproduct with multiple variables
 

Try this:

=SUMPRODUCT((A1:A6={"A","C"})*(B1:B6={"B","D"})*C1 :C6)



"sam" wrote:

hi everyone,

here is my issue. i am using SUMPRODUCT for a couple of criteria and
returning the sum of another column. E.g.

A B 1
A C 2
B C 3
B E 4
C A 5
C D 5

=SUMPRODUCT((A1:A6="A")*(B1:B6="B"),C1:C6)+SUMPROD UCT((A1:A6="C")*(B1:B6="D"),C1:C6)
= 1 + 5
= 6

This all works fine but I was wondering if there was another option so i
would not have to use SUMPRODUCT multiple times. It is essential that A is
matched with B and that C is matched with D.

Thanks

Sam

sumproduct with multiple variables
 

Try Pivot table

"sam" wrote:

hi everyone,

here is my issue. i am using SUMPRODUCT for a couple of criteria and
returning the sum of another column. E.g.

A B 1
A C 2
B C 3
B E 4
C A 5
C D 5

=SUMPRODUCT((A1:A6="A")*(B1:B6="B"),C1:C6)+SUMPROD UCT((A1:A6="C")*(B1:B6="D"),C1:C6)
= 1 + 5
= 6

This all works fine but I was wondering if there was another option so i
would not have to use SUMPRODUCT multiple times. It is essential that A is
matched with B and that C is matched with D.

Thanks

Sam

sumproduct with multiple variables
 

You are right...I missed that..


"T. Valko" wrote:

That doesn't meet the criteria:

It is essential that A is matched with B and
that C is matched with D.

--
Biff
Microsoft Excel MVP


"Jacob Skaria" wrote in message
...
Try the below

=SUMPRODUCT(ISNUMBER(MATCH(A1:A6,{"A","C"},0))*
ISNUMBER(MATCH(B1:B6,{"B","D"},0)),C1:C6)

If this post helps click Yes
---------------
Jacob Skaria


"sam" wrote:

hi everyone,

here is my issue. i am using SUMPRODUCT for a couple of criteria and
returning the sum of another column. E.g.

A B 1
A C 2
B C 3
B E 4
C A 5
C D 5

=SUMPRODUCT((A1:A6="A")*(B1:B6="B"),C1:C6)+SUMPROD UCT((A1:A6="C")*(B1:B6="D"),C1:C6)
= 1 + 5
= 6

This all works fine but I was wondering if there was another option so i
would not have to use SUMPRODUCT multiple times. It is essential that A
is
matched with B and that C is matched with D.

Thanks

Sam


.