SUMPRODUCT Formula
 

When i change 2300,2400 with a cell refernece , excel gives me error.
Please advise.
"Bob Phillips" wrote in message
...
Shashi,

=SUMPRODUCT((DataCustNum={2300,2400})*
(DataOrderDate=Period1Start)*
(DataOrderDate<=Period1End)*
(DataAmount)


--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks


"Shashi Bhosale" wrote in message
...
=SUMPRODUCT((DataCustNum=A11)*
(DataOrderDate=Period1Start)*
(DataOrderDate<=Period1End)*
(DataAmount)

Above Formula works Great if A11 is having only one value, example 23000

If if my Cell A11 contains 23000, 24000
How i can change my formula to check for both values (23000 & 24000)

Thanks in advance,

Shashi

SUMPRODUCT Formula
 

Shashi,

Put 2300 in A11, 2400 in B11, and use

=SUMPRODUCT((DataCustNum=A11:B11)*
(DataOrderDate=Period1Start)*
(DataOrderDate<=Period1End)*
(DataAmount)

--

HTH

Bob Phillips

"Shashi Bhosale" wrote in message
...
When i change 2300,2400 with a cell refernece , excel gives me error.
Please advise.
"Bob Phillips" wrote in message
...
Shashi,

=SUMPRODUCT((DataCustNum={2300,2400})*
(DataOrderDate=Period1Start)*
(DataOrderDate<=Period1End)*
(DataAmount)


--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks


"Shashi Bhosale" wrote in message
...
=SUMPRODUCT((DataCustNum=A11)*
(DataOrderDate=Period1Start)*
(DataOrderDate<=Period1End)*
(DataAmount)

Above Formula works Great if A11 is having only one value, example
23000

If if my Cell A11 contains 23000, 24000
How i can change my formula to check for both values (23000 & 24000)

Thanks in advance,

Shashi

SUMPRODUCT Formula
 

Hi Bob,

I can not use cell A11 and B11. Sometimes i may have more values (2300,
2400, 2500 ........and so on).
I have to use only one cell to store the values.

Please let me know if you have any other method which i can use.

Thanks,

Shashi.


"Bob Phillips" wrote in message
...
Shashi,

Put 2300 in A11, 2400 in B11, and use

=SUMPRODUCT((DataCustNum=A11:B11)*
(DataOrderDate=Period1Start)*
(DataOrderDate<=Period1End)*
(DataAmount)

--

HTH

Bob Phillips

"Shashi Bhosale" wrote in message
...
When i change 2300,2400 with a cell refernece , excel gives me error.
Please advise.
"Bob Phillips" wrote in message
...
Shashi,

=SUMPRODUCT((DataCustNum={2300,2400})*
(DataOrderDate=Period1Start)*
(DataOrderDate<=Period1End)*
(DataAmount)


--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks


"Shashi Bhosale" wrote in message
...
=SUMPRODUCT((DataCustNum=A11)*
(DataOrderDate=Period1Start)*
(DataOrderDate<=Period1End)*
(DataAmount)

Above Formula works Great if A11 is having only one value, example
23000

If if my Cell A11 contains 23000, 24000
How i can change my formula to check for both values (23000 & 24000)

Thanks in advance,

Shashi

SUMPRODUCT Formula
 

Shashi,

This will check for any valises in row 11

=SUMPRODUCT((DataCustNum=A11:OFFSET(A11,0,COUNTA(A 11:IA11)-1))*(DataOrderDat
e=Period1Start)*(DataOrderDate<=Period1End)* (DataAMount))

Are we there?

--

HTH

Bob Phillips

"Shashi Bhosale" wrote in message
...
Hi Bob,

I can not use cell A11 and B11. Sometimes i may have more values (2300,
2400, 2500 ........and so on).
I have to use only one cell to store the values.

Please let me know if you have any other method which i can use.

Thanks,

Shashi.


"Bob Phillips" wrote in message
...
Shashi,

Put 2300 in A11, 2400 in B11, and use

=SUMPRODUCT((DataCustNum=A11:B11)*
(DataOrderDate=Period1Start)*
(DataOrderDate<=Period1End)*
(DataAmount)

--

HTH

Bob Phillips

"Shashi Bhosale" wrote in message
...
When i change 2300,2400 with a cell refernece , excel gives me error.
Please advise.
"Bob Phillips" wrote in message
...
Shashi,

=SUMPRODUCT((DataCustNum={2300,2400})*
(DataOrderDate=Period1Start)*
(DataOrderDate<=Period1End)*
(DataAmount)


--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks


"Shashi Bhosale" wrote in message
...
=SUMPRODUCT((DataCustNum=A11)*
(DataOrderDate=Period1Start)*
(DataOrderDate<=Period1End)*
(DataAmount)

Above Formula works Great if A11 is having only one value, example
23000

If if my Cell A11 contains 23000, 24000
How i can change my formula to check for both values (23000 &
24000)

Thanks in advance,

Shashi

SUMPRODUCT Formula
 

I can not use the complete row, i am restricted to use only one cell (ex.
A11).

"Bob Phillips" wrote in message
...
Shashi,

This will check for any valises in row 11


=SUMPRODUCT((DataCustNum=A11:OFFSET(A11,0,COUNTA(A 11:IA11)-1))*(DataOrderDat
e=Period1Start)*(DataOrderDate<=Period1End)* (DataAMount))

Are we there?

--

HTH

Bob Phillips

"Shashi Bhosale" wrote in message
...
Hi Bob,

I can not use cell A11 and B11. Sometimes i may have more values (2300,
2400, 2500 ........and so on).
I have to use only one cell to store the values.

Please let me know if you have any other method which i can use.

Thanks,

Shashi.


"Bob Phillips" wrote in message
...
Shashi,

Put 2300 in A11, 2400 in B11, and use

=SUMPRODUCT((DataCustNum=A11:B11)*
(DataOrderDate=Period1Start)*
(DataOrderDate<=Period1End)*
(DataAmount)

--

HTH

Bob Phillips

"Shashi Bhosale" wrote in message
...
When i change 2300,2400 with a cell refernece , excel gives me
error.
Please advise.
"Bob Phillips" wrote in message
...
Shashi,

=SUMPRODUCT((DataCustNum={2300,2400})*
(DataOrderDate=Period1Start)*
(DataOrderDate<=Period1End)*
(DataAmount)


--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks


"Shashi Bhosale" wrote in message
...
=SUMPRODUCT((DataCustNum=A11)*
(DataOrderDate=Period1Start)*
(DataOrderDate<=Period1End)*
(DataAmount)

Above Formula works Great if A11 is having only one value,
example
23000

If if my Cell A11 contains 23000, 24000
How i can change my formula to check for both values (23000 &
24000)

Thanks in advance,

Shashi