nested if problem

[Dave]

Hi, i have been trying to solve a problem for ages and i really need
help!!
ive got two spreadsheets:
first one is an income spreadhseet that shows when money is ndue, how
much and who from, format as below:
Company Name Due Date Amount
SSSSS 01/07/2004 £50

The second spreadsheet is a cashflow sheet that will have income and
expenditure on a monthly basis. format below:
Company name Apr May Jun July Aug etc
ssssss x


what i want is query that will link the two together so that i can
write a query in the cell marked x to search the income spreadsheet
for any amount of money coming from any given company, then check what
dates the money is due and if the due date is between 01/04/2004 and
30/04/2004 then return the amount of monye due in the cell marked x
otherwise return 0. ive tried using nested ifs but i cant quite solve
the problem. i was trying to set it up so that it searched the column
company name in income spreadhseet and if it match the company name on
the cashflow spreadsheet then it would seach for if money was due in a
certain month, then return the money in the amount cell or 0..... not
sure if ive explained it ok but its driving me mad and its been a long
week!!! any suggestions greatfly appreciated

cheers

Dave

 

[Frank Kabel]

Hi
try
=SUMPRODUCT(--('income'!$A$1:$A$1000="company_name"),--('income'!$B$1:$
B$1000>=DATE(2004,4,1)),--('income'!$B$1:$B$1000´<=DATE(2004,4,30)),'in
come'!$C$1:$C$1000)

 

[Raeven]

There are actually two ways of doing this, depending on what you use th
spreadsheet for. The first is the conditional sum wizard. I've foun
that this easily creates the array formula very easily.

Assume that your table containing data is on a worksheet called "Data
and your monthly is on one called "Monthly."

Go to Tools>Wizard>Conditional Sum.

Select your data table from the Data worksheet in the first step.

In the next step, your column to sum will be the one which contains th
dollar amount ("Amount").

In this same step, your first condition for January will be that th
Paid Date Column Is >= 1/1/04 (you can type this in). Click Add afte
you enter each condition. The second will be that it Is <=1/31/04.
The last will be that the company name matches the company you want i
to.

In the next step, choose to only show the result of the formula. Next
Pick a cell and Finish.

You can copy this cell to other cells and type in the appropriate dat
in place of the dates and company. Remember, though, it is an arra
formula, and you have to hit *Ctrl-Shift+Enter* after editing th
formula.

The other method is to create a pivot table.

You will need to first create another column in the data table tha
indicates just the month, instead of the date.

Then, go to:
Data>Pivot Tabel and PivotChart Report...

Again, select your data table as the source data.

Your layout should look like the attached document

Attachment filename: monthly expense.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=61980