Expanded sumproduct
 

I have this and it works fine:

=SUMPRODUCT(($A$14:$A$30002=$B$1)*($B$14:B$30002=$ A$3)*($C$14:$C$30002=$C$2)*($D$14:$D$30002))

And this works fine if I want more than one value from column A:
=SUMPRODUCT(SUMIF(A14:A30002,{"A","B","C"},D14:D30 002))

But how do I include the the column B and C columns and criteria into the
2nd formula.

Thank you,

Steven

Expanded sumproduct
 

I may not be understanding what you want but try
=SUMPRODUCT(A14:A30002={"A","B","C"}),D14:D30002)


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Steven" wrote in message
...
I have this and it works fine:

=SUMPRODUCT(($A$14:$A$30002=$B$1)*($B$14:B$30002=$ A$3)*($C$14:$C$30002=$C$2)*($D$14:$D$30002))

And this works fine if I want more than one value from column A:
=SUMPRODUCT(SUMIF(A14:A30002,{"A","B","C"},D14:D30 002))

But how do I include the the column B and C columns and criteria into the
2nd formula.

Thank you,

Steven

Expanded sumproduct
 

What I am looking for is that the 1st function has sumproduct on three
different columns but the criteria is only one item. The 2nd function has
one column but multiple criteria How do I make the formula so I can sum on
multiple criteria in column A ; and then also include in the function columns
B and C and their criteria.

Thank you,

Steven

"Don Guillett" wrote:

I may not be understanding what you want but try
=SUMPRODUCT(A14:A30002={"A","B","C"}),D14:D30002)


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Steven" wrote in message
...
I have this and it works fine:

=SUMPRODUCT(($A$14:$A$30002=$B$1)*($B$14:B$30002=$ A$3)*($C$14:$C$30002=$C$2)*($D$14:$D$30002))

And this works fine if I want more than one value from column A:
=SUMPRODUCT(SUMIF(A14:A30002,{"A","B","C"},D14:D30 002))

But how do I include the the column B and C columns and criteria into the
2nd formula.

Thank you,

Steven

Expanded sumproduct
 

Basically:

Why does this not work:

=SUMPRODUCT((SUMIF($A$14:$A$30002,{"A","B"}))*($B$ 14:B$30002=$A$3)*($C$14:$C$30002=$C$2)*($D$14:$D$3 0002))

where column D are the amounts.

Thank you,


Steven

"Steven" wrote:

What I am looking for is that the 1st function has sumproduct on three
different columns but the criteria is only one item. The 2nd function has
one column but multiple criteria How do I make the formula so I can sum on
multiple criteria in column A ; and then also include in the function columns
B and C and their criteria.

Thank you,

Steven

"Don Guillett" wrote:

I may not be understanding what you want but try
=SUMPRODUCT(A14:A30002={"A","B","C"}),D14:D30002)


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Steven" wrote in message
...
I have this and it works fine:

=SUMPRODUCT(($A$14:$A$30002=$B$1)*($B$14:B$30002=$ A$3)*($C$14:$C$30002=$C$2)*($D$14:$D$30002))

And this works fine if I want more than one value from column A:
=SUMPRODUCT(SUMIF(A14:A30002,{"A","B","C"},D14:D30 002))

But how do I include the the column B and C columns and criteria into the
2nd formula.

Thank you,

Steven

Expanded sumproduct
 

=SUMPRODUCT(($A$14:$A$30002={"A","B","C"})*($B$14: B$30002=$A$3)
*($C$14:$C$30002=$C$2)*$D$14:$D$30002)
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Steven" wrote in message
...
I have this and it works fine:


=SUMPRODUCT(($A$14:$A$30002=$B$1)*($B$14:B$30002=$ A$3)*($C$14:$C$30002=$C$2)
*($D$14:$D$30002))

And this works fine if I want more than one value from column A:
=SUMPRODUCT(SUMIF(A14:A30002,{"A","B","C"},D14:D30 002))

But how do I include the the column B and C columns and criteria into the
2nd formula.

Thank you,

Steven

Expanded sumproduct
 

Yes, Thank you very much.

"Ragdyer" wrote:

=SUMPRODUCT(($A$14:$A$30002={"A","B","C"})*($B$14: B$30002=$A$3)
*($C$14:$C$30002=$C$2)*$D$14:$D$30002)
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Steven" wrote in message
...
I have this and it works fine:


=SUMPRODUCT(($A$14:$A$30002=$B$1)*($B$14:B$30002=$ A$3)*($C$14:$C$30002=$C$2)
*($D$14:$D$30002))

And this works fine if I want more than one value from column A:
=SUMPRODUCT(SUMIF(A14:A30002,{"A","B","C"},D14:D30 002))

But how do I include the the column B and C columns and criteria into the
2nd formula.

Thank you,

Steven

Expanded sumproduct
 

One last question:

Instead of {"A","B","C"} is there a way to use cell references? I have
tried many things without sucess.

Thank you,

Steven


"Steven" wrote:

Yes, Thank you very much.

"Ragdyer" wrote:

=SUMPRODUCT(($A$14:$A$30002={"A","B","C"})*($B$14: B$30002=$A$3)
*($C$14:$C$30002=$C$2)*$D$14:$D$30002)
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Steven" wrote in message
...
I have this and it works fine:


=SUMPRODUCT(($A$14:$A$30002=$B$1)*($B$14:B$30002=$ A$3)*($C$14:$C$30002=$C$2)
*($D$14:$D$30002))

And this works fine if I want more than one value from column A:
=SUMPRODUCT(SUMIF(A14:A30002,{"A","B","C"},D14:D30 002))

But how do I include the the column B and C columns and criteria into the
2nd formula.

Thank you,

Steven

Expanded sumproduct
 

Hi,

The only problem with this solution is you can't put cell references within
{}. So if you want to use cell references here is one solution:

=SUMPRODUCT(($A$14:$A$30002=TRANSPOSE(B1:B3))*($B$ 14:B$30002=$A$3)*($C$14:$C$30002=$C$2)*($D$14:$D$3 0002))

Then the formula requires array entery - Shift+Ctrl+Enter
If the reference cell B1:B3 are switched to B1:D1 (a row) then

=SUMPRODUCT(($A$14:$A$30002=B1:D1)*($B$14:B$30002= $A$3)*($C$14:$C$30002=$C$2)*($D$14:$D$30002))

without array entry.

--
Thanks,
Shane Devenshire


"Steven" wrote:

Yes, Thank you very much.

"Ragdyer" wrote:

=SUMPRODUCT(($A$14:$A$30002={"A","B","C"})*($B$14: B$30002=$A$3)
*($C$14:$C$30002=$C$2)*$D$14:$D$30002)
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Steven" wrote in message
...
I have this and it works fine:


=SUMPRODUCT(($A$14:$A$30002=$B$1)*($B$14:B$30002=$ A$3)*($C$14:$C$30002=$C$2)
*($D$14:$D$30002))

And this works fine if I want more than one value from column A:
=SUMPRODUCT(SUMIF(A14:A30002,{"A","B","C"},D14:D30 002))

But how do I include the the column B and C columns and criteria into the
2nd formula.

Thank you,

Steven

Expanded sumproduct
 

Shane,

Thank you for your response.

Is there a limit of using only one TRANSPOSE in the formula. I have tried
with additional and I cannot make it work.

Steven

"ShaneDevenshire" wrote:

Hi,

The only problem with this solution is you can't put cell references within
{}. So if you want to use cell references here is one solution:

=SUMPRODUCT(($A$14:$A$30002=TRANSPOSE(B1:B3))*($B$ 14:B$30002=$A$3)*($C$14:$C$30002=$C$2)*($D$14:$D$3 0002))

Then the formula requires array entery - Shift+Ctrl+Enter
If the reference cell B1:B3 are switched to B1:D1 (a row) then

=SUMPRODUCT(($A$14:$A$30002=B1:D1)*($B$14:B$30002= $A$3)*($C$14:$C$30002=$C$2)*($D$14:$D$30002))

without array entry.

--
Thanks,
Shane Devenshire


"Steven" wrote:

Yes, Thank you very much.

"Ragdyer" wrote:

=SUMPRODUCT(($A$14:$A$30002={"A","B","C"})*($B$14: B$30002=$A$3)
*($C$14:$C$30002=$C$2)*$D$14:$D$30002)
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Steven" wrote in message
...
I have this and it works fine:


=SUMPRODUCT(($A$14:$A$30002=$B$1)*($B$14:B$30002=$ A$3)*($C$14:$C$30002=$C$2)
*($D$14:$D$30002))

And this works fine if I want more than one value from column A:
=SUMPRODUCT(SUMIF(A14:A30002,{"A","B","C"},D14:D30 002))

But how do I include the the column B and C columns and criteria into the
2nd formula.

Thank you,

Steven

Expanded sumproduct
 

"ShaneDevenshire" wrote...
....
=SUMPRODUCT(($A$14:$A$30002=TRANSPOSE(B1:B3))*($B $14:B$30002=$A$3)*
($C$14:$C$30002=$C$2)*($D$14:$D$30002))
....

Or as long as A14:A30002 don't contain wildcard characters,

=SUMPRODUCT(COUNTIF(B1:B3,$A$14:$A$30002)*($B$14:B $30002=$A$3)*
($C$14:$C$30002=$C$2)*($D$14:$D$30002))

Expanded sumproduct
 

Try this ... needs only regular entry:

=SUMPRODUCT(($A$14:$A$30002=T(INDIRECT({"B1","B2", "B3"})))
*($B$14:B$30002=$A$3)*($C$14:$C$30002=$C$2)*$D$14: $D$30002)

Assumes values in B1, B2, and B3 are Text.
If they're numeric, change the "T" in front of Indirect to an "N".

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"Steven" wrote in message
...
One last question:

Instead of {"A","B","C"} is there a way to use cell references? I have
tried many things without sucess.

Thank you,

Steven


"Steven" wrote:

Yes, Thank you very much.

"Ragdyer" wrote:

=SUMPRODUCT(($A$14:$A$30002={"A","B","C"})*($B$14: B$30002=$A$3)
*($C$14:$C$30002=$C$2)*$D$14:$D$30002)
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit
!
---------------------------------------------------------------------------
"Steven" wrote in message
...
I have this and it works fine:


=SUMPRODUCT(($A$14:$A$30002=$B$1)*($B$14:B$30002=$ A$3)*($C$14:$C$30002=$C$2)
*($D$14:$D$30002))

And this works fine if I want more than one value from column A:
=SUMPRODUCT(SUMIF(A14:A30002,{"A","B","C"},D14:D30 002))

But how do I include the the column B and C columns and criteria into
the
2nd formula.

Thank you,

Steven

Expanded sumproduct
 

Am I missing something here?

What's wrong with ISNUMBER(MATCH(...))

--
Biff
Microsoft Excel MVP


"Harlan Grove" wrote in message
...
"ShaneDevenshire" wrote...
...
=SUMPRODUCT(($A$14:$A$30002=TRANSPOSE(B1:B3))*($ B$14:B$30002=$A$3)*
($C$14:$C$30002=$C$2)*($D$14:$D$30002))
...

Or as long as A14:A30002 don't contain wildcard characters,

=SUMPRODUCT(COUNTIF(B1:B3,$A$14:$A$30002)*($B$14:B $30002=$A$3)*
($C$14:$C$30002=$C$2)*($D$14:$D$30002))

Expanded sumproduct
 

Harland,

The countif is a WOW.

Thank you,

Steven

"Harlan Grove" wrote:

"ShaneDevenshire" wrote...
....
=SUMPRODUCT(($A$14:$A$30002=TRANSPOSE(B1:B3))*($B $14:B$30002=$A$3)*
($C$14:$C$30002=$C$2)*($D$14:$D$30002))
....

Or as long as A14:A30002 don't contain wildcard characters,

=SUMPRODUCT(COUNTIF(B1:B3,$A$14:$A$30002)*($B$14:B $30002=$A$3)*
($C$14:$C$30002=$C$2)*($D$14:$D$30002))