using sumif to sum based on a variable in a cell

[Mike Rosenberg]

This one has to be dumb on my part, but....

I have a worksheet with a chart of the accounts, by number.

I have a second worksheet with a list of transactions
showing the account number and amount (it's in date order)

I have a worksheet with a chart of the accounts, by number.

I want to create a 3rd worksheet that sums up the
transactions by account (using the chart of accounts)

Simple example:

Sheet1 (Chart of accounts)
A B
(2) Account Description
(3) 4100 Gross Sales
(4) 4200 Cost of Goods Sold

Sheet2(transactions)
(A) (B) (C)
(2) Date Account Amount
(3) 9/1/03 4100 5000.00
(4) 9/1/03 4200 4250.00
(5) 9/2/02 4100 3000.00
(6) 9/2/03 4200 2750.00

Sheet3 (Summary)
(A) (B)
(2) Account Total
(3) =Sheet1!A3 ["4100"] ?????
(4) =Sheet1!A4 ["4200"] ?????

For sheet3, B3 I tried
=SUMIF(SHEET2!B3:B6,"=A3",SHEET3$C3:C6)
and get 0 -- since, I suspect, it is matching to the
literal A3, not cell A3

Thanks.

 

[Leo Heuser]

Mike

You're quite right. Try this one instead:


=SUMIF(SHEET2!B3:B6,"="&A3,SHEET3$C3:C6)
or simply
=SUMIF(SHEET2!B3:B6,A3,SHEET3$C3:C6)
since it's equal to. With > , >=, < ,<= you have to put
the signs inside double quotes.

--
Best Regards
Leo Heuser
MVP Excel

Followup to newsgroup only please.

 

[Leo Heuser]

You're welcome, Mike.
I was of course referring to the literal "A3"

LeoH