Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Numbers - IF, SUMIF, COUNTIF, WHAT IF??

I have a column of dollar figures. A correlating column of multiple choice options. And a total amount to achieve.


I4:I10 - Dollar figures

J4:J10 - Multiple Choice Options

H3 = Total Dollar Amount


If any cell in range J4:J10 = "Square" or "Square 0%", then that correlating dollar figure in column I should not be tallied in H3.


Ex.

User uploaded file


I need a formula that tallies the sum of the figures in column I EXCEPT when a cell in column J = "Square" or "Square 0%".


So, in this case, the total "Earnings" should be $209.

MacBook Pro (Retina, 13-inch,Early 2015), macOS Sierra (10.12.3)

Posted on Jan 1, 2018 10:12 PM

Reply
Question marked as Top-ranking reply

Posted on Jan 1, 2018 10:43 PM

SUMIF will do it.


I added a row containing an amount and type "Square 0%" as you had included that type in your exclude criteria, but no such row in your example.

User uploaded file

The formula uses the 'not equal to' ( <> ) comparison operator, and the wild card * , meaning 'any characters or no characters' following enough of Square or Square 0% to dintinguish both terms from the others in the type list.


I did not test the placing of this formula in a merged cell, as shown in your example. Merged cells are a thing to be avoided in general practice.


Formula: SUMIF(J,"<>Squar*",I)


Regards,

Barry

3 replies
Sort By: 
Question marked as Top-ranking reply

Jan 1, 2018 10:43 PM in response to cornfused

SUMIF will do it.


I added a row containing an amount and type "Square 0%" as you had included that type in your exclude criteria, but no such row in your example.

User uploaded file

The formula uses the 'not equal to' ( <> ) comparison operator, and the wild card * , meaning 'any characters or no characters' following enough of Square or Square 0% to dintinguish both terms from the others in the type list.


I did not test the placing of this formula in a merged cell, as shown in your example. Merged cells are a thing to be avoided in general practice.


Formula: SUMIF(J,"<>Squar*",I)


Regards,

Barry

Reply

Jan 2, 2018 2:01 AM in response to cornfused

"What if I had three exceptions? "Square", "Square 0%", and "Deferred"?"


If you could be certain that no other type label would contain the two letters "re" in that order, you could just change the current formula to:


SUMIF(J,"<>*re*",I)


But that's cutting it pretty fine.


You'd be better to switch to SUMIFS:


SUMIFS(I,J,"<>Squar*",J,"<>Deferred")


Or, since you' be using SUMIFS anyway, skip the wild card and specify each of the 'skip this' types in full:


SUMIFS(I,J,"<>Square",J,"<>Square %",J,"<>Deferred")


Regards,

Barry

Reply

Numbers - IF, SUMIF, COUNTIF, WHAT IF??

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.