Self calcualting spreedsheets for qtrly reports

N

Nikolatos091199

I need to be able to self calculate totals for the
following headings within a date range (qtr):

GRIEF(E3), PTSD(F3), OTHER(G3), UNDER 18(H3), ADULTS(I3),
SENIORS(J3), Indiv.(K3), Grp(L3), Fam.(M3), Eval(N3),
Intake(03), M Mgmt(P3), New(Q3), Cont(R3), SDate(S3), EDate
(T3)

for example: Within the date range 1/1/04 to 3/31/04 how
many New etc? By being able to create a formula/function
to do this it will help me buy saving days of resorting
the data by each heading (within the above date range) and
manually count.

I assume I will need to come up with an IF Formula, is
this correct? If so, can someone help with its creation,
as I am out of practice (at least 5 years)...any help I
can get on this would be greatly appreciated.

Thanks in advance!
 
F

Frank Kabel

Hi Nikolatos

I assume that SDate is your starting date.
Try the following:
=SUMPRODUCT((R4:R9999>=DATE(2004,1,1))*(R4:R9999<DATE(2004,3,31)))
this will count all entries with and SDate >=1/1/04 and <3/31/04

HTH
Frank
 
N

Nikolatos091199

Sorry, but just to clarify, if I use this formula will it
count the date column to calculate how many total entries
within the date range have been made or will I be able to
specify what (column(s)/total(s))? I need it to calculate
totals per heading/columnwhich match the criteria of the
date range specified.
 
F

Frank Kabel

Hi
The formula below will calculate ALL entries in column R which fall
into the date range. If you want to count only specific entries (based
on the value of other columns) you can use the following:
=SUMPRODUCT((R4:R9999>=DATE(2004,1,1))*(R4:R9999<DATE
(2004,3,31))*(Q4:Q9999="New"))
this will count all entries for the specified date range that have the
word 'New' in column Q

HTH
Frank
 
N

Nikolatos091199

Thanks I'll try this!
-----Original Message-----
Hi
The formula below will calculate ALL entries in column R which fall
into the date range. If you want to count only specific entries (based
on the value of other columns) you can use the following:
=SUMPRODUCT((R4:R9999>=DATE(2004,1,1))*(R4:R9999<DATE
(2004,3,31))*(Q4:Q9999="New"))
this will count all entries for the specified date range that have the
word 'New' in column Q

HTH
Frank



.
Click to expand...
 
Top