Forecasting using SUMPRODUCT and dates

  • Thread starter Thread starter rerhart
  • Start date Start date
R

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((D2:D200>1/31/2004,SUMPRODUCT((B2:B200="MN")*(C2:C200="Web"))

For February:
IF((D2:D200>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
 
Hi
try something like
=SUMPRODUCT((D2:D200>DATE(2004,1,31))*(B2:B200="MN")*(C2:C200="Web"))
 
Frank,

After our conversation with Norman earlier this week

=SUMPRODUCT((D2:D20>(--("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)
 
Bob said:
Frank,

After our conversation with Norman earlier this week

=SUMPRODUCT((D2:D20>(--("2004-01-31")))*(B2:B20="MN")*(C2:C20="Web"))
Click to expand...


:-)
yes I remember....
Frank
 
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.
 
Obviously VB works - I think you should send Frank and I a case each, and it
may work for us too.

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

Bob
Click to expand...

Bob
very good idea <vbg>

Norman: I think UPS can deliver this in a couple of days <ebg>
Frank
 
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.
 
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
 
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)
 
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
 
-> 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?
Click to expand...

Yes that is good enough.

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

You could but not recommended. It will imapir performance.
 
I entered the following formula:

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

But get #N/A for a result??

=SUMPRODUCT((D2:D200>(--("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 (D2:D200
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!
-
 
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)
 
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
 
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)
 
Back
Top