Formulas in Numbers

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

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

" 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