Formula Problem - 3 conditions

  • Thread starter Thread starter montana
  • Start date Start date
M

montana

I am trying to count the number of occurences in my spreadsheet that
have several conditions.

In Sheet 1 I have...
Col A = "name" Col B = "Prod code" Col C = Date

In sheet 2 I have...
The months Jan04 thru Dec04 in Columns A - L
and product codes in rows 2 - 10

Under each month I want to count the number of times the name, product
code and Date(in column C) fall within a particular month.

Something like this..( eg Jan)

If in sheet 1 col A = "name" and col B = "prod code" and the date in
Col C is beteween 01.01.2004 and 01.31.2004 then place count under
Jan04.

I hope my explanation is adequate so I may get some help with this
formula problem.

Any suggestion will be greatly appreciated. Thanks in advance.
 
Hi
try
=SUMPRODUCT(('sheet1'!A1:A100="name")*('sheet1'!B1:B100="prod_code")*('
sheet1'!C1:C100>=DATE(2004,1,1))*('sheet1'!C1:C100<DATE(2004,2,1)))

You can replace the hardcoded conditions with cell references. Just be
carfull if you compare dates.
 
Hi Montana!

You actually have 4 condition!

Try:

=SUMPRODUCT(--($A$1:$A$6="Widget"),--($B$1:$B$6="A234"),--($C$1:$C$6>=
DATE(2004,1,1)),--($C$1:$C$6<=DATE(2004,1,31)))

SUMPRODUCT calculates the sum of the products of the arguments used.
In the above all of the arguments return TRUE or FALSE and the use of
the -- serves to coerce the arguments to 1 or 0. Only if all arguments
are TRUE will you get a result for a row of 1*1*1*1 and thereby cause
an addition to the product.

For simple counting forms you can use:

=SUMPRODUCT(($A$1:$A$6="Widget")*($B$1:$B$6="A234")*($C$1:$C$6>=DATE(2
004,1,1))*($C$1:$C$6<=DATE(2004,1,31)))

You'll note use of absolute referencing of the ranges and these should
have the same number of cells. Rather that use (eg) "Widget" you would
be much better off using (eg) A10 where A10 countains the name. This
facilitates copying down the formula to get a table for the different
names and product codes between pairs of dates.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top