CODEWITHSUNDEEP
excelexcel-formula
Roland Pokornyik
Roland Pokornyik

Reputation: 105

Excel - Sumif with two conditions

I would like to SUM revenues by product type in a given month.

Data is as follows:

A: name of client
B: month
C: product type
D: revenue

I would like to have a summary table where months are on the top (B1 - January, C1 - February, etc.) and product type is on the left (A2 - Type 1, A3 - Type 2, A4 - Type 3).

Can you please help me to figure out the right formula?

Upvotes: 0

Views: 177

Answers (2)

user4039065
user4039065

Reputation:

This isn't really an answer, but there is too much for a comment.

  1. Use real dates in B1:M1, not January, February, etc. Start with 1/1/2014 in B1 and then put =EDATE(B1, 1) in C1 and fill right to M1. Finally, select B1:M1 and tap Ctrl+1. Go to the Numbers tab and choose Custom from the list down the left. Use mmm or mmmm for the Type:. This way you will have real dates that you can use in calculations that will look like Jan or January.

  2. Use combinations of relative and absolute cell references in your first formula in B2. When you refer to the date in B1, use B$1. When you refer to the product type in A2 use $A2. These will lock the first row for the dates and the first column for the product types while allowing the remainder of the respective cell references to 'float' relatively.

  3. Your product data dates are probably not all the first of the month so a SUMIFS will have to capture all of the dates within a month. Let's say that your product types are in Sheet3's column D and the dates are in Sheet3's column F and the amounts are in Sheet3's column H. Start your first formula in the summary sheet's B2 with something like:

    =SUMIFS(Sheet3!$H:$H, Sheet3!$D:$D, $A2, Sheet3!$F:$F, ">="&B$1, Sheet3!$F:$F, "<"&EDATE(B$1, 1))

You should be able fill or copy that formula both right and down to catch all months and all product types.

Upvotes: 1

JNevill
JNevill

Reputation: 50034

You'll probably want to check out =Sumifs(). It's a multiple condition version of =Sumif()

=Sumifs(Sheet1!$D:$D, Sheet1!$C:$C, $A1, Sheet1!$B:$B, B$1)

Stick that in B2 of your summary table, and change Sheet1 to be whatever sheet your data is in.

Upvotes: 1

Related Questions