You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

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

Formulas in Numbers

I am trying to write a SUMIFS formula to tabulate different products from an imported table, one of my columns contains values like such as "Size": "200ml" or "Size": "375ml" and "Color": "Blue" or "Color": "Red" These values include the quotation marks and the colon and when I try to write the condition for SUMIF I cannot make it work. Do I need to try to remove the extra quotation marks or is there a way for the formula to recognize the value?


Thanks.

MacBook Pro 15″, macOS 10.15

Posted on Jan 9, 2021 1:08 PM

Reply
Question marked as Top-ranking reply

Posted on Jan 10, 2021 7:17 AM

This is great Barry and it works and gets me almost all the way to the place that I need to be. Lets say I have in column A products such as Tee shirts, Baseball Caps, and Hoodies and then in column C I have quantities of each variant that a customer might have ordered, 1, 2, 3, etc. The formula that I am writing would tell me how many blue hoodies that have been ordered for the year and have a COUNTIF nested in a SUMIF? I am going to experiment and see if I can do it myself also

5 replies
Question marked as Top-ranking reply

Jan 10, 2021 7:17 AM in response to Barry

This is great Barry and it works and gets me almost all the way to the place that I need to be. Lets say I have in column A products such as Tee shirts, Baseball Caps, and Hoodies and then in column C I have quantities of each variant that a customer might have ordered, 1, 2, 3, etc. The formula that I am writing would tell me how many blue hoodies that have been ordered for the year and have a COUNTIF nested in a SUMIF? I am going to experiment and see if I can do it myself also

Jan 10, 2021 2:23 AM in response to patkelly4321

Hi Pat,


Do you really want to "SUM" the colours and sizes?


Counting the colours seems more likely (and then, perhaps, summing the counts).


Taking another look at it, COUNTING the occurrences of each colour (and by extension, of each size) turned out to be fairly simple.


As written, the content of each 'colour' cell is a text string that is the same for every instance of each colour.


Put a copy of that text string (copied from one of the rows that contains it, and COUNTIF will happily use a cell reference to the cell you paste that copied value into as the search key for counting that colour:


Regards,

Barry

Jan 9, 2021 7:05 PM in response to patkelly4321

The first question that arises is 'why does the data contain both the "name" of the value AND the value itself?


Usual practice would be to have a column labelled "Size" in the column Header cell, with sizes of the items listed in the body cells on that column.

Similarly, the "Colour" column would contain only the name of the colour of the item on each row.


Quotation marks re used in formulas to tell the formula it is looking for the text value consisting of the text enclosed by the quotes. If the formula is looking for "Red", it will find that value in cells containing the text Red. I suspect those superfluous double quotes in the cells are causing the issue you describe.


Regards,

Barry



Jan 9, 2021 8:23 PM in response to Barry

Thanks for the reply. The data is downloaded from my ecommerce website in this format and yes those quotation marks are making it hard for me to enable the formula to find the correct text. If I could figure out how to remove the quotation marks I would do that or is there a way to search for the word red or the number 200 in a cell? Can I use single quotation marks? It is too hard for me to have to try to clean up the data before I download it into Numbers.


Thanks

Jan 10, 2021 1:35 PM in response to patkelly4321

" The formula that I am writing would tell me how many blue hoodies that have been ordered for the year "

Since the number ordered is in a separate column, and each row does not indicate an order of a single item, you will need a SUMIFS formula for this.


For the total number of blue hoodies ordered in the year, you will need to SUM the numbers column, choosing only the numbers from rows matching four conditions:

The Date of the order must be on or after January 1 of the year being summed

The date of the order myst be before January 1 of the following year.'

The above two conditions may be ignored if the table listing the orders contains only orders for the year being summed.

The product must be a hoodie.

The colour must be blue


The syntax for SUMIFS is similar to that for COUNTIFS, but differs in some aspects. Find and read the syntax in the Function Browser, and use the hints available in the Formula editor when you insert the function name by clicking it's styled name as shown by the editor as a choice,


Further questions welcomed as they become needed.


Regards,

Barry

Formulas in Numbers

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