How to sum values based on two criterias-CodePudding

I'm trying to sum values based on two criteria: category and month (Renew). I want to sum categories by renew month. So, for things renewed in March, sum separately by category, but what is monthly and have the same category, must be summed too.

Example table

For example: I have Softwares / Apps / Sites category that have different renew dates (March, April, May, June, September and also monthly). So, I need to separate and sum values by month, but what are "monthly" have to be summed on all months. Like this:

Softwares / Apps / Sites category

march: 26,50

april: 24,90

may: 30,60

june: 64

september: 24,90

Hope I made it clear... I'm going crazy with that... please, help me...

CodePudding user response：

For March:

=SUMPRODUCT(B2:B1000*(C2:C1000="Softwares / Apps / Sites")*(((E2:E1000="Monthly") (E2:E1000="March"))>0))

Categories and month can be refer to cell. In the picture I can't see column, change them with real layout.

CodePudding user response：

My table data is this:

Description,Price,Category,Period,Renew,Day
Netflix.Com,9,Streaming,Monthly,Monthly,5
Disney Plus,11,Streaming,Monthly,Monthly,5
Amazonprime,8,Streaming,Monthly,Monthly,5
Youtube,8,Softwares/Apps/Sites,Monthly,Monthly,5
Spotify,6,Softwares/Apps/Sites,Monthly,Monthly,5
Google,15,Work,Monthly,Monthly,5
Bank Fees,5,Bank Taxes,Monthly,Monthly,5
Internet,20,Streaming,Monthly,Monthly,5
Healthcare,20,Health,Monthly,Monthly,5
Smart Fit,35,Academy,Annual,June,10
Cellphone Plan,20,Work,Annual,June,10
Backup sw,12.5,Softwares/Apps/Sites,Annual,March,20
Office 365,50,Softwares/Apps/Sites,Annual,June,15
Truecaller,10.9,Softwares/Apps/Sites,Annual,September,10
Kasperksy,10.9,Softwares/Apps/Sites,Annual,April,10
Canva,5.7,Work,Annual,May,15
Kasperksy,10.9,Softwares/Apps/Sites,Annual,May,15
Canva,5.7,Work,Annual,May,5

(To make it easier to answer your questions, please export the data as CSV, then use four leading spaces to format the data in your question - it saves me five minutes typing in the data, and potentially making errors).

The command to use is SUMIFS. The syntax is:

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

So, if the entire table is located between A1 and F19, I could do this:

=SUMIFS($B$1:$B$19, $C$1:$C$19,"Softwares/Apps/Sites",$D$1:$D$19,"Monthly")

With my data, a total of 14.