I have this formula:

=ROUND(sumif(Percent, ">0")/COUNTIF(Percent, ">0"), 3)*100 & "%" & " : " & ROUND(sumif(Percent, "<0")/COUNTIF(Percent, "<0"), 3)*100 & "%"

That displays two percentages (ex: 4.5% : -3.9%) that correctly calculates the percentage averages from the percentage column.

I have another column, called Style, which has three types of letter combinations (BRB, EP, and RM).

What I am having difficulty with is recreating the above formula but that only picks up all the BRB, EP, and RM separately and displays their averages aside from the total averages.

Logically, it seems straightforwards with an IF() and an AND() to pick up the correct rows but I can't seem to get it to work.

This is what I have (this is just the left half of the top formula):

=ROUND(sumif(AND(Percent= ">0",Style="BRB")/COUNTIF(AND((Percent= ">0",Style="BRB")) 3)*100))


=if(and(sumif(H12, ">0")=True, sumif(I12,"BRB")=true), sum(I12))

Percent is my H3:H named range, and Style is my I3:I range.


i think i got it, using sumifs

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.