If row contains the correct month then add amount in row B to total. Otherwise don't

[marko2002]

I know someone will have the answer to this, but I don’t know if I’ll make a hash of explaining it!.

Here goes …

I have a spreadsheet with my inventory of books that I sell, and I have the date sold in there as well as cost price, sale price and profit, etc - but I’m looking to create another sheet which will give me running totals on a month to month basis on my profit, preferably with a total of books sold too. So, if I have sheet 1 showing the date sold in column “B”, quantity in column “C” and the profit in column “D”, I’d like to have sheet 2 to give me totals, so for example, if I had 30 entries (rows) on sheet 1 showing I have sold 30 books in August, in sheet 2 I’d like it to show the total items sold (i.e. calculates totals in column “C” where the month “August” has been used in column “B”, and totals in column “D” where the month “August” has been used in column “B”. The same would be applied for the other months but once I have the formula for one month I’d be able to work out the rest.

Hope this all makes sense and look forward to the replies
All the best
Mark

[Lupp]

Hopefully you will agree:
I changed the subject slightly. In common interpretation something like "month" is interpreted as meaning the literal string. However, you were talking of the month as a part of the date.
As I assume you enter the dates as usual, they will in addition be numeric. The month August should then also not be represented by the text “August” but by the number 8. This holds even if you formatted cells containg dates to show names of months.by MMMM format code.

[Lupp]

Use SUMIFS() or SUMPRODUCT.

=SUMIFS(AmountsRange;MONTH(DateRange);ReferenceToCellContainingTheMonthNumber)
e.g. shown after entering for array-evaluation with Ctrl+Shift+Enter as: 
{=SUMIFS($Sheet1.$D$2:$D$1001;MONTH($Sheet1.$B$2:$B$1001);$F$1)}

See also this attached example.

(BTW: The variant using SUMPRODUCT() should even work if not explicitly entered for array-evaluation. It doesn’t, however. a wee little bug.)

[marko2002]

Thank you for your answer, I did have a little trouble using your original formula and I’ve absolutely no doubt whatsoever it was me doing something wrong but strangely I managed to get the SUMPRODUCT working in the following way:

=SUMPRODUCT(MONTH(Books.C12:C1200)=8,Books.R12:R1200)

Where “Books” is the name of the sheet, “C” is the column for the dates, “8” is the month and R is the column for the amounts

Thanks again for your help, muchly appreciated
Mark