Excel: Cross-reference w/ logic

June 23, 2011 at 14:49:25
Specs: Windows 7
How do I cross reference worksheets and write formulas so they self-update to the destination sheet? I have a multi-sheet excel document where I am trying to create a self-updating budget worksheet for our home finances.

There is a "transactions sheet" where I have these columns:
Year/ Date/ Budget Category/ Amount/ Payment Type/ Description

Some of the columns contain text, others dates, and others numbers formatted like this: $100.00.

I also have a "Budget sheet" where I would like to have the information go and be analyzed. I can't figure out how to take only one "Budget Category" and only one "Month" from the "transactions sheet" and have the total of all spending in that category appear in one cell in the Budget Sheet.

For example, I would like to track how much I spent on groceries in May 2011. So, I need all amounts in the Amount column that match this row criteria: Date=May1-31, 2011 AND Budget Category=Groceries.

Can anyone help me?

See More: Excel: Cross-reference w/ logic

June 23, 2011 at 16:01:30
What version of Excel are you using?

If it's 2007 or above, try the SUMIFS function.

If your "transactions sheet" looks like this:

    A       B              C              D          E              F        
1 Year    Date      Budget Category     Amount   Payment Type   Description
2        5/1/2011       Groceries        $100
3        5/2/2011       Groceries        $100
4        5/4/2011       Gas              $50
5        6/1/2011       Groceries        $100
6        5/21/2011      Groceries        $100

This formula will return $300 for all groceries purchased in May:

=SUMIFS(D2:D6, B2:B6, ">="&DATEVALUE("5/1/2011"),
B2:B6,"<"&DATEVALUE("6/1/2011"), C2:C6,"Groceries")

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

June 23, 2011 at 16:34:31
How does this pull information from another sheet? I believe the formula is correct but I get an error message when I hit enter.

This is what I am entering:


Should there be spaces in the formula?

The "Transactions!" part is what I'm not sure about.

Thanks for you help-- best I've gotten so far.

Report •

June 23, 2011 at 18:18:08
My apologies...I assumed you knew how to create formulas "across sheets" and just needed a method for summing data based on conditions.

I provided a formula that works for a single sheet just to keep it simple.

Do this as a lesson:

In a new workbook, click in any cell in Sheet1 and type this, but do not hit the Enter key:


Now take your mouse, click on the sheet tab for Sheet2 and then click and drag down from A1 to A10.

Finally, click the Check mark next to your formula.

Note how Excel placed the Sheet name before the range, added the single quotes, the exclamation point, etc.

Whenever you reference a range in another sheet, you have to use that syntax - for each and every range you reference. That's how Excel knows where to get the data from.

Try my lesson, then see if you can modify the formula I offered by either manually entering the sheet name or by letting Excel do it by clicking the sheet tab and dragging down the cells.

If you have any trouble, let us know and we'll see what we can do to help.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

June 23, 2011 at 21:13:17
Thanks again, I think I'm getting closer.

Here's my current formula on Sheet 2
=SUMIFS(Sheet1!G2:G112,Sheet1!B2:B112,">&DATEVALUE(5/1/2011), Sheet1!B2:B112, "<DATEVALUE("6/1/2011"),Sheet1!E2:E112,"Groceries")

I don't receive an error message when I hit enter, but the amount is displayed as $0, which I know is not correct.

Sheet 1's Headings are
A | B | C | D | E | F | G | H |
Year | Date | Day | Heading | Category | Recipient| Amount | Payment Type|

2011 | 5-2 | Mon | Food | Groceries| Kroger | $23.40 | VISA/0001 |
2011 | 4-22 | Fri | Housing| Rent | Landlord | $700.00 | Check #1234 |
2011 | 2-7 | Mon | Debt |Car Pmt. | Citibank | $249.00 | Check #1200 |


So let's say that, according to the above data sample, I want the result to be $23.40, and it's currently coming out as $0.

Any thoughts?

I have Office 2007, by the way.

Report •

June 24, 2011 at 01:08:51
Look at my formula:

=SUMIFS(D2:D6, B2:B6, ">="&DATEVALUE("5/1/2011"),
B2:B6,"<"&DATEVALUE("6/1/2011"), C2:C6,"Groceries")

vs. yours:

=SUMIFS(Sheet1!G2:G112,Sheet1!B2:B112,">&DATEVALUE(5/1/2011), Sheet1!B2:B112, "<DATEVALUE("6/1/2011"),Sheet1!E2:E112,"Groceries")

Note specifically the differences in these portions of your formula:

Mine: ">="&DATEVALUE("5/1/2011") and "<"&DATEVALUE("6/1/2011")

Yours: ">&DATEVALUE(5/1/2011) and "<DATEVALUE("6/1/2011")

1 - You have the quotation marks in the wrong places.
2 - You didn't use the Concatenation operator (&) like I did.
3 - Even if you had items 1 and 2 correct, you are only checking for dates greater than "5/1/2011" not greater than or equal to "5/1/2011" so the formula will ignore "5/1/2011".

You need to separate out the operators (>, etc.) as text by using the quotes and then concatenate that with the DATEVALUE function. If you put the DATEVALUE function inside the quotes, Excel will think that it is just text and not evaluate the function.

You should review both functions (SUMIFS and DATEVALUE) in the Excel help files so you know how each one works and the correct syntax for each. Once you understand how each one works on its own, you'll have a better understanding how to combine them for your use.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

June 24, 2011 at 10:58:48
Thanks so much! Now I understand better and it's working!

Report •

Ask Question