Newbie =sumproduct help needed.

N

NiFreaky

I haven't done any Excel spreadsheets before. I have now set up a ne
department in work and have created a spreadsheet that is used fo
logging correspondence that comes into the office. There was a bi
backlog and I have split the spreadsheet into 2 sheets, one for on
type of correspondence and the other for another type. There is abou
130 rows in one and 50 in the other.

Each of these rows include 15 columns that include reference numbers
names, addresses, dates etc. There is no calculation type data. th
last 2 columns are general text fields, one (Column L) that says wher
the resulting file has been sent to and one (column N) that says th
file is now complete/actioned/no further action, etc.

Every 2 weeks I have to compile a report stating 1. How many are stil
to be looked at. 2. How many are sent to other departments but no
finished. 3. How many are outstanding. 4. Total actioned out of all th
rows on both sheets.

What I am trying to do is create a third sheet that has all th
calculations on it and display the report. I can do this manually b
adding autofilters and firstly filtering the blanks from N then th
blanks from L. This gives me the figure reqired at 1. above. After tha
it gets complicated!

I have looked at =countif and =rows without much success. From readin
these forums I think =sumproduct might be a better choice but I ain
got a clue how to use it.

Also, do I just set the whole column in the calculation i.e. N:
because I am always adding new items of correspondence? Or is there
way to automatically increase the active area everytime I add a ne
row?


Phew, that was complicated to explain. It would be so much easier t
show someone who knew what they were doing
 
B

Bob Phillips

Something like

1. =SUMPRODUCT(--(Sheet1!L1:L1000=""))

2. =SUMPRODUCT(--(SHeet1!L1:L1000<>""),--(Sheet1!N1:N1000=""))

3. What's the difference between this and 1?

4. =SUMPRODUCT(--(SHeet1!L1:L1000<>""),--(Sheet1!N1:N1000<>""))

Just repeat for other sheet and sum the two.

--

HTH

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

NiFreaky

Bob,

Thanks, that did the trick! I had to "tinker" with the ="" and <>"" t
get the right results. But at least I kind of understand how this work
now.

Thanks again
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top