sumifs and "or" statement

[plamb]

Trying to figure out one little piece of a formula is maddening but thank goodness I have you guys to turn to. You've been a big help to me so far so thank you!

I have a tab with data (Running data) that I am trying to summarize in another tab (INSYTE). I'm having trouble with the formula in col J in the INSYTE tab.

Rather than hard coding the last criteria of the sumifs (18049008--this is a GL number), I want to say to add certain GLs when they have a debit transaction (tran_code=3) and subtract when they have a credit transaction (tran_code=1). Of course it isn't that easy though! This logic works on balance sheet accounts (GL numbers less than 40000000). On income statement accounts I need the logic to be reversed.

I tried putting the GL #s that I'm dealing with in the Info tab (A20:A34) but haven't been successful in figuring out how to use them in my formula. When I attempted an "or" statement, I got nothing when there should be something.

Any help would be greatly appreciated!
Peg

[NBVC]

to use the info on the Info tab.. the SUMIFS formula would be:

=SUMPRODUCT(SUMIFS(tran_amt,calc_acct_no,A3,tran_code,3,account,Info!$A$20:$A$34))

this is how you create an "OR" scenario with SUMIFS

Is that what you need?

[plamb]

yes, thanks! I didn't know that about sumproduct.

I appreciate the quick reply.