Thread: sum cell based on criteria countif formula assistance

I have been working on getting some help on this for some days now and I have been reading and reading to see if I could figure it out.
I think im on the right track, but not sure how to complete this (if im even on the correct track)

I also have a post at http://www.ozgrid.com/forum/showthread.php?t=161722

So i have this so far and not sure if the "IF" function is the way to go? or if im not even in the ball park
It seems easier when its only numbers, but when it comes to text, im getting myself confused. not to mention the lack of knowledge with excel.
obviously the red part is wrong/not complete.

=COUNTIFS($I$6:$I$37,$C$10,$G$6:$G$37,$D$8,IF($G$6:$G$37="a",>,$H$6:$H$37="b",0))

test.xlsx

Originally Posted by vlady

Count no of A, count numbers of B.is that right?
Then what will be the expected outcome??

Not the "total" of A's in column G. Im trying to compare column G to H. So in theory, if there are more A's to B's then D10 will sum that to a 1.
Then you can see in the example there is another matrix to compare(G28:H37) which should state B's also out count A's...so E10 counts the comparison as a whole matrix comparison...think of it as a Win or Lose situation.. out of "X" amount of games...you win some and the other team wins some...but as a whole, which team came out with "the WIN"

am i making sense?

not quite, but at least its a start.

you have more A's to B's in G17:H26 so the B colums should show a 1 in D12 , E12 should be a 0

As XYZ change as im putting a new modified example up..youll see how the numbers dont work...



notice the numbers changed when i changed I28 to a "Y". Now Im comparing G6:H14 and adding it to the other comparison of G28:H37
D11 should be 0 & E11 should have turned to a 2

What part dont you understand?...I can make it look like a win loss comparison to maybe make it easier to understand if you want.
let me know and thanks so much for the assistance so far.

test.xlsx

=IF($I$6="X",IF(COUNTIF(G$6:G$15,"A")>COUNTIF($H$6:$H$15,"B"),1,0),0)+IF($I$17="X",IF(COUNTIF($G$17:$G$26,"A")>COUNTIF($H$17:$H$26,"B"),1,""),0)+IF($I$28="X",IF(COUNTIF($G$28:$G$37,"A")>COUNTIF($H$28:$H$37,"B"),1,0),0)

if we change that so not just one matrix criteria is being calculated (red part of formula) and all of coulmn I is according to text (XYZ) data..

When i was evaluating the equation i think your only comparing X's matrix...i could be wrong..im just learning these complex formulas
But im going to follow up tomorrow..again thanks for your time so far.

If you change Column I to all Z's, X's, or Y's...not all them are being evaluated.
Im going look at the formula when i get home from work...
thanks again, this is the furthest i have gotten with this in about 2 weeks. Great help cant wait to get back to it later today.

[QUOTE=NBVC;2691282]Does this help?

Add a helper column to show expand the "x", "y", "z" to all columns per group.

NBVC,
not quite. Vlady was closer in testcopyvlady.xlsx


I was wondering how to have all understand this better at work today and thought of this.

Im going to relate this to a play off situation..Im changing this up so XYZ will be teams 1 through how evermany...and A,B will be wins and losses
Hopefully this clears up the idea.

Ultimately Im trying to get a "best of 5" games wins to losses...Whereas Players play opponents in a sport...if players have more wins to losses in a best of 5 games..the Team will take a WIN , if not, its a Loss (When i say "team", im refering to my team, not team1,2,3 so on they are opponents in my new example)

The teams wins and losses totals go on the left D10:E13 when the corresponding team has played..ie Team1 Team2 so on..Playing a team more then once will happen so I have to be sure theres variables in the formula being able to recognize that and sum the results as data continues.


Hows this new revised example with the same concept with the results.
Thanks

Attachment 138164

Does this help?

Add a helper column to show expand the "x", "y", "z" to all columns per group.

So in J6 add:

=LOOKUP(REPT("z",99),I$6:I6)

copied down

Then formula in D10 copied down:

=MAX(0,COUNTIFS($J$6:$J$37,$C10,G$6:G$37,D$8)-COUNTIFS($J$6:$J$37,$C10,H$6:H$37,E$8))

and I am not sure if you want the reverse for E10:

=MAX(0,COUNTIFS($J$6:$J$37,$C10,H$6:H$37,E$8)-COUNTIFS($J$6:$J$37,$C10,G$6:G$37,D$8))

I think I know what your trying to accomplish..but im not trying to compete XYZ against each other according to A's<=>B's.