Sumif confusion

[Shelly]

I posted yesterday and read someone else's posting today
with the same (or a similar?) question but I still do not
understand how to use sumif. It seems to involve sumif
but when I tried the formula suggested, I could not get
it to work. Let me try to explain again.

I have 4 sheets. Sheet 1, 2 and 3 are line item reports,
one for each bank account. Sheet 4 is a master report.

In sheets 1, 2, and 3: each row is a transaction, and two
columns I need to look at and use are:

(sheet1)
amount category
$505 rent
$45 fees
$45 fees
$720 travel
$505 rent
$600 travel

Sheet 4, the master report has a column for each sheet
(account) and rows for each category.

sheet1 sheet2 sheet3 total (I can do the total)
rent
fees
travel

What formula would I type into sheet4 to return the value
1010 for sheet 1 rent?

 

[Norman Harker]

Hi Shelly!

Try:
=SUMIF(Sheet1!$B$2:$B$7,Sheet4!A$2,Sheet1!$A$2:$A$7)
Returns 1010

The first argument of SUMIF is the range that contains the data being
looked at.
The second argument is the criteria that we are using to determine if
the entry is to be used
The third argument is the range to be summed and is optional. If
omitted it will sum the first rage based on the criteria.

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

 

[Paul]

I posted yesterday and read someone else's posting today
with the same (or a similar?) question but I still do not
understand how to use sumif. It seems to involve sumif
but when I tried the formula suggested, I could not get
it to work. Let me try to explain again.

I have 4 sheets. Sheet 1, 2 and 3 are line item reports,
one for each bank account. Sheet 4 is a master report.

In sheets 1, 2, and 3: each row is a transaction, and two
columns I need to look at and use are:

(sheet1)
amount category
$505 rent
$45 fees
$45 fees
$720 travel
$505 rent
$600 travel

Sheet 4, the master report has a column for each sheet
(account) and rows for each category.

sheet1 sheet2 sheet3 total (I can do the total)
rent
fees
travel

What formula would I type into sheet4 to return the value
1010 for sheet 1 rent?

Assuming your headings are in row 1 and column A, you will be putting the
formula in B2.
I would use
=SUMPRODUCT( (Sheet1!$A$2:$A$7) * (Sheet1!$B$2:$B$7=$A2))

You can then copy the formula from B2 into B3 and B4, and just edit the
sheet names.
Then you can then copy these three formulas down to rows3 and 4 to
automatically get the fees and travel totals from each sheet.