Inserting Extra Equation Parameter

Hello,

How do make the following formula to retrieve only the positive or negative values only instead of the total sum ?


=SUMIF(F11:F85,E96,(G11:G85))


I believe i should add this in the above formula in the bold section but cant seem to do it right.
,"<0" or ,">0"

Try

=SUMIFS(G11:G85,F11:F85,E96,G11:G85,">0")

Note: with SUMIFS, summed value is first range

Hi John,


Thanks for the help ! that formula resolved my issue.
Pity i cant drag it across but have to manually edit the cells inside for those i want to remain fixed.


Thanks again !
Warm Regards.

Which cells do you want fixed?

=SUMIFS($G$11:$G$85,$F$11:$F$85,E$96,$G$11:$G$85,">0")

will fix ranges and keep the criteria (E96) on row 96 but will change to F etc if you drag across columns.

Try

=SUMIFS(G11:G85,$F$11:$F$85,$E$96,G11:G85,">0")

OR

=SUMIFS(G$11:G$85,$F$11:$F$85,$E$96,G$11:G$85,">0")

so rows 11:85 are fixed (ABSOLUTE reference) but columns are RELATIVE i.e adjust if to drag formula acroos columns


Yes .. using $ sign will keep row or column or both fixed

Search for RELATIVE and ABSOLUTE references in the web to understand the differences