Sum of both a) Sumif with multiple criteria + b) positive totals

[tsnguyen]

Hi,

I'm trying to automate some formulas without using long formulas or name manager.

My issue is I have 5x "criteria" (same column), but need to SUM only those criteria with an overall positive balance(balance sheet).

I can't use this formula below because it doesn't consider the closing balance per criteria (assets/liability).
=SUM(SUMIF(X:X,{"Criteria1","Criteria2","Criteria3","Criteria4","Criteria5"},P:P))

My current solution:-
=IF(SUMIF(X:X,"Criteria1",P:P)>0,SUMIF(X:X,"Criteria1",P:P),0) + IF(SUMIF(X:X,"Criteria2",P:P)>0,SUMIF(X:X,"Criteria2",P:P),0) + IF(SUMIF(X:X,"Criteria3",P:P)>0,SUMIF(X:X,"Criteria3",P:P),0) + IF(SUMIF(X:X,"Criteria4",P:P)>0,SUMIF(X:X,"Criteria4",P:P),0) + IF(SUMIF(X:X,"Criteria5",P:P)>0,SUMIF(X:X,"Criteria5",P:P),0).

Any feedback would be appreciated!

Cheers, T

[FDibbins]

Hi, welcome to the forum

very 1st question...

I'm trying to automate some formulas without using long formulas or name manager.

Why??

I will take a look at what you have, but it would probably be easier if you could upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.