SUMIF a field if box unchecked

Annie Dunn

I'm trying to sum a column, [Total Commission], if [Rep] is ES and the [Rep Paid] column does NOT have a check mark.

It's looking like this so far:

 

=SUMIFS([TOTAL COMMISSION]:[TOTAL COMMISSION], [SALES REP]:[SALES REP], "ES", [REP PAID]:[REP PAID], ??? (this is where I get lost.)

Help?

Andrée Starå

Hi Annie,

Try this.

=SUMIFS([TOTAL COMMISSION]:[TOTAL COMMISSION]; [SALES REP]:[SALES REP]; "ES"; [REP PAID]:[REP PAID]; 0)    

The same version but with the below changes for your and others convenience.  

=SUMIFS([TOTAL COMMISSION]:[TOTAL COMMISSION], [SALES REP]:[SALES REP], "ES", [REP PAID]:[REP PAID], 0)

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Annie Dunn

Hi Andree,

 

This did not appear to work. Any other ideas?

Andrée Starå

That's strange! It works here!

Can you share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Best,

Andrée

Annie Dunn

I agree - it seems like it should work! If 0 is the indicator for a box being unchecked, that should be the right formula. Could it possibly because the total commission column is not currently up-to-date throughout the entire spreadsheet? It should only matter if there is a value in the cell, not if the formula within the cell is active, correct? I've attached a screenshot.

 

 

Annie Dunn

Hi Andree,

I tried that same formula referencing this sheet from another sheet and not it seems to work. I'm not sure what the glitch was before, but as long as it's working somewhere I'm OK with it! Thanks for your help.

Andrée Starå

Excellent!

Happy to help!

Let me know if the glitch comes back!

Best,

Andrée