# Excel 2007: possible VBA, or can I use functions?

Discussion in 'Business Applications' started by themike, Apr 15, 2011.

Not open for further replies.

Joined:
Apr 15, 2011
Messages:
1
For an example, I am looking to get D7 on the 'WS' worksheet to look through both column B and C of the 'AJE' worksheet and find anywhere there is the label of the value of A7(from WS!), and then sum the values of column D - column E on AJE! for only those corresponding rows

I then want to carry this down for each cell of column D & E on 'WS'

I'm not sure if this is something fairly easy, or not. I do know some VBA (if I have to go that route) but haven't done much within excel: so even how to get started would be great.

Thanks!

File size:
30 KB
Views:
55
2. ### Jimmy the Hand

Joined:
Jul 28, 2006
Messages:
1,223
I'm sure I don't understand the task, but here's a formula into D7:
Code:
`=SUMIF(AJE!B:B,WS!\$A7,AJE!D:D)+SUMIF(AJE!C:C,WS!\$A7,AJE!E:E)`
Jimmy

Joined:
Jan 16, 2010
Messages:
93
Think you have a decent enough understanding Jimmy

Opened up the example sheet and think I understood a lot more. I've done something similar before so it helps.

What I think you want to do in plain English is:

In cell (WS) D7 you want to total all of the Debits from the AJE tab if they match the Account Title Column.
In cell (WS) E7 you want a similar thing but to total all the Credits from the AJE tab.

In this case the following formulas should work

D7
=SUMIF(AJE!\$B:\$B, WS!\$A7, AJE!\$D:\$D) + SUMIF(AJE!\$C:\$C, WS!\$A7, AJE!\$D:\$D)

E7
=SUMIF(AJE!\$B:\$B, WS!\$A7, AJE!\$E:\$E) + SUMIF(AJE!\$C:\$C, WS!\$A7, AJE!\$E:\$E)

Even given it a quick test on the example sheet and all works fine from what I can see.
Hope this helps!

As Seen On