Forecasting using SUMPRODUCT and dates

[rerhart]

I am having a problem with counting data that is within a certain date.
for example, I have 4 columns. SERVERNAME, LOCATION, TYPE
LEASEENDDATE. I am currently using SUMPRODUCT to count the number o
servers I have for a particular location and type.

A B C D
SERVERNAME LOCATION TYPE LEASEENDDATE
Server1 MN Web 6/30/2004
Server2 CA App 8/15/2004

SUMPRODUCT((B2:B200="MN")*(C2:C200="Web"))

On another sheet, I would like to forecast of how many servers I hav
that are not expired yet for every month of the year and have 1
columns for each month, JAN, FEB, MAR, etc. I am thinking of a formul
like:

For January:
IF((D2200>1/31/2004,SUMPRODUCT((B2:B200="MN")*(C2:C200="Web"))

For February:
IF((D2200>2/29/2004,SUMPRODUCT((B2:B200="MN")*(C2:C200="Web"))

How do I ONLY count the number of 'Web' servers in 'MN' that have no
yet exprited as of (date in column D)?

Thanks

 

[Frank Kabel]

Hi
try something like
=SUMPRODUCT((D2200>DATE(2004,1,31))*(B2:B200="MN")*(C2:C200="Web"))

 

[Bob Phillips]

Frank,

After our conversation with Norman earlier this week

=SUMPRODUCT((D220>(--("2004-01-31")))*(B2:B20="MN")*(C2:C20="Web"))


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Frank Kabel]

Frank,

After our conversation with Norman earlier this week

=SUMPRODUCT((D220>(--("2004-01-31")))*(B2:B20="MN")*(C2:C20="Web"))

yes I remember....
Frank

 

[Norman Harker]

Hi Frank and Bob!

A very productive week! Sorted out European fast date entry and
discovered the uses of --"2004-04-10" date entry.



--
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.

 

[Bob Phillips]

Obviously VB works - I think you should send Frank and I a case each, and it
may work for us too.

Bob

 

[Frank Kabel]

Obviously VB works - I think you should send Frank and I a case each,
and it may work for us too.

Bob

Bob
very good idea <vbg>

Norman: I think UPS can deliver this in a couple of days <ebg>
Frank

 

[Norman Harker]

Hi Frank!

I've got a couple of slabs, but it's like Guinness and just doesn't
travel. You'll have to come and drink it here.

Bob's had too much already celebrating Arsenal's win!

I sent the European date solution to Chip complete with the
attribution to VB (Victoria Bitter).
--
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.

 

[rerhart]

Excellent. That works. Thank you very much.

In addition to this formula, how would I implement --within the sam
formula-- the automatic counting of of rows in my sheet? ..instead o
choosing a range of (for example) B2:B200, have my SUMPRODUCT formul
automatically count and utilize rows that are filled since the amoun
of rows of data in my sheet changes daily. Also, there will always b
data in column A, no blanks.

Thx

 

[Bob Phillips]

Will those rows have blanks or not? If not, just change B2:B200 to
B2:OFFSET(B1,COUNTA(B:B)-1,0), etc.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[rerhart]

Will those rows have blanks or not? If not, just change B2:B200 t
B2:OFFSET(B1,COUNTA(B:B)-1,0), etc.


-> Column B will not have blanks, but others may have blanks. I assum
it would not matter and I only need to count rows using data from on
column that has no blanks, correct?

Could I also put a range of B2:B65536 to cover the entire spreadsheet?

Thx

 

[Bob Phillips]

-> Column B will not have blanks, but others may have blanks. I assume
it would not matter and I only need to count rows using data from one
column that has no blanks, correct?

Yes that is good enough.

Could I also put a range of B2:B65536 to cover the entire spreadsheet?

You could but not recommended. It will imapir performance.

 

[rerhart]

I entered the following formula:

=SUMPRODUCT((D2200>(--("12/31/2003")))*(B2:OFFSET(B1,COUNTA(B:B)-1,0)="MN")*(C2:C200="Web"))

But get #N/A for a result??

=SUMPRODUCT((D2200>(--("12/31/2003")))*(B2:B200="MN")*(C2:C200="Web"))

...Works fine other than I would like to...
within the same formula, how can I get rid of all my ranges (D2200
B2:B200, C2:C200, etc.) and replace the formula with something tha
auto counts filled-in rows...keeping in mind that column B will have n
blanks?

Thanks!
-

 

[Bob Phillips]

All of the ranges must be the same size. I thought that was what you
referred to in the previous post.

Try this

=SUMPRODUCT((D2:OFFSET(D2,COUNTA(B:B)-1,0>(--("2003/12/31")))*(B2:OFFSET(B1,
COUNTA(B:B)-1,0)="MN")*(C2:OFFSET(C2,COUNTA(B:B)-1,0="Web"))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[rerhart]

Once again, thank you very much for your help.

With the follwoing formula:

=SUMPRODUCT((D2:OFFSET(D2,COUNTA(B:B)-1,0>(--("12/31/2003")))*(B2:OFFSET(B1,COUNTA(B:B)-1,0)="MN")*(C2:OFFSET(C2,COUNTA(B:B)-1,0="Web"))))

I am now receiving a #VALUE error. Not sure where the problem is.

Here is my sample data:

Server Name Location Type Lease End Date
Server1 MN Web 6/30/2004
Server2 CA App 8/15/200

 

[Bob Phillips]

This works for me. It is imperative that you enter the date in the correct
format as I have done

=SUMPRODUCT((D2:OFFSET(D2,COUNTA(B:B)-1,0)>--("2003/12/31"))*(B2:OFFSET(B2,C
OUNTA(B:B)-1,0)="MN")*(C2:OFFSET(C2,COUNTA(B:B)-1,0)="Web"))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)