Sum based on month and name columns

Question

14.08.2015  John Doe        100
14.08.2015  Bruce Willis    100
09.08.2015  Keanu Reaves    100
15.08.2015  John Doe        250
18.08.2015  John Doe        150
18.08.2015  Keanu Reaves    200
20.08.2015  Keanu Reaves    150
27.08.2015  Bruce Willis    100
06.09.2015  Bruce Willis    100
06.09.2015  Bruce Willis    100
11.09.2015  Keanu Reaves    100
13.09.2015  John Doe        200
14.09.2015  Bruce Willis    100
18.09.2015  John Doe        300
19.09.2015  Keanu Reaves    400

I have a spreadsheet that looks like this (3 columns), and I am trying to create a sum of third column based on the first and second ones. I would like to see the total for user 'John Doe' in 8th month. So far i came up with this:

=sumif(if(month(A:A)=8),"John Doe",C:C)

but it returns 0 (it should actually return 500). Any idea on how should I tweak this?

Answer 1

By using SUMIFS (multiple criteria to define the sum range) instead of SUMIF (one criteria to define the sum range) and adding an extra column B which calculates the month number you can find that sum. The extra column is required because SUMIFS doesn't allow formulas like MONTH() as a criteria.

=SUMIFS(D1:D15,B1:B15,"8",C1:C15,"John Doe")

Answer 2

The following formula will sum the values based on the month and name.

Formula

=QUERY({ARRAYFORMULA(TEXT(DATE(RIGHT(DATA!A:A, 4), MID(DATA!A:A, 4, 2), LEFT(DATA!A:A, 2)), "mmmm")), DATA!B:C}, "SELECT Col1, Col2, SUM(Col3) GROUP BY Col1, Col2 LABEL Col1 'month', Col2 'name'")

Screenshot

result

Explained

First the date notation needs to be rearranged (DATE) and then turned into the corresponding month name (TEXT) plus the other columns are added to the data range of the QUERY. After that the QUERY is GROUPED BY the month and the name and the corresponding summation of the values.

Example

I've created an example file for you: sum based on month and name

Answer 3

Simpler, provided ColumnA is Date format (which for Romania is possible):

=query(A:C;"select Sum(C) where month(A)=7 and B='John Doe'")

QUERY