Check Date Range and Multiply Stock Qty
 

Hi all,

Further to Petes successful resolution of my earlier query (thanks
again Pete) I am now looking for the following info:

where A = Date Column (date)
where B = Model Column (text and number)
where C = Qty of Model (number)

Pete gave me the following formula to count the number of products in
each month:

=SUMPRODUCT((MONTH(A1:A1000)=1)*(YEAR(A1:A1000)=20 08)*(B1:B1000="Ec80"))

However I now need to count the TOTAL number of products i.e. the
above formula multiplied by the qty in stock of each model

Date Model Qty
01/01/08 EC80 10
20/01/08 EC80 5
31/01/08 ZX40 7

So using the formula above I get "2" as the result

I then need to use this to sum the total of Column C which should give
me a result of "15"

Thanks in advance!!

Jon

Check Date Range and Multiply Stock Qty
 

=SUMPRODUCT((MONTH(A1:A1000)=1)*(YEAR(A1:A1000)=20 08)*(B1:B1000="Ec80"),(c1:c1000))

--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Jon" escreveu:

Hi all,

Further to Petes successful resolution of my earlier query (thanks
again Pete) I am now looking for the following info:

where A = Date Column (date)
where B = Model Column (text and number)
where C = Qty of Model (number)

Pete gave me the following formula to count the number of products in
each month:

=SUMPRODUCT((MONTH(A1:A1000)=1)*(YEAR(A1:A1000)=20 08)*(B1:B1000="Ec80"))

However I now need to count the TOTAL number of products i.e. the
above formula multiplied by the qty in stock of each model

Date Model Qty
01/01/08 EC80 10
20/01/08 EC80 5
31/01/08 ZX40 7

So using the formula above I get "2" as the result

I then need to use this to sum the total of Column C which should give
me a result of "15"

Thanks in advance!!

Jon

Check Date Range and Multiply Stock Qty
 

Okay, Jon, to sum column C with the same criteria you need to change
the formula to:

=SUMPRODUCT((MONTH(A1:A1000)=1)*(YEAR(A1:A1000)=20 08)*(B1:B1000="Ec80")*(C1:C1000))

Hope this helps.

Pete

On Jan 18, 11:09*am, Jon wrote:
Hi all,

Further to Petes successful resolution of my earlier query (thanks
again Pete) I am now looking for the following info:

where A = Date Column (date)
where B = Model Column (text and number)
where C = Qty of Model (number)

Pete gave me the following formula to count the number of products in
each month:

=SUMPRODUCT((MONTH(A1:A1000)=1)*(YEAR(A1:A1000)=20 08)*(B1:B1000="Ec80"))

However I now need to count the TOTAL number of products i.e. the
above formula multiplied by the qty in stock of each model

Date * * * * * * * * Model * * * * * * * Qty
01/01/08 * * * * * EC80 * * * * * * * * 10
20/01/08 * * * * * EC80 * * * * * * * * *5
31/01/08 * * * * * ZX40 * * * * * * * * * 7

So using the formula above I get "2" as the result

I then need to use this to sum the total of Column C which should give
me a result of "15"

Thanks in advance!!

Jon