SUMIF with two criterias: Sum within a given time frame and if a group of cells do match

[Liberty Prime]

Hi there

I hope you can help me with the following thing I'm (unsuccessfully) dealing with.

I'm updating on a daily basis a report with a list of values based on a date, values which I want to sum for a given time frame, though only if they match certain criteria (with other words, the sum of all the expenses from day 1st of the month to for example the day 14th, attributed to the account A). The account will be always the same, but the time frame will be changing and the upper limit selected from a dropdown list (the minimum will be always the day 1 of the month). It seems like a SUMIF with two criterias, but a bit more complicated than usual.

For another report I have a different formula that may help in this case and I use to pull the values of a cell for an exact day. If I could add a SUMIF there it would be awesome. It seems as follows:

=INDEX('Input'!G:G,MATCH(A7&B7(DATE(YEAR('Input'!$B$2),MONTH(DATEVALUE('Input'!$B$2)),$C$2)),'Input'!A:A&'!B:B&'Input'!C:C,0)))


As a legend:

* Input!G:G is the column with the expenses
* A7&B7 the account to be checked
* Input!$B$2 contains the month, year and starting date of the range
* $C$2 is the cell with the day to be checked
* Input!C:C is the column containing the date

Thanks in advance!

 

[Scott T]

Do you have SUMIFS on your version of excel? That would be the easiest way.
https://support.office.com/en-us/article/sumifs-function-c9e748f5-7ea7-455d-9406-611cebce642b

If you do not have SUMIFS

Book1
	B	C
4	1/1/2019	1
5	1/7/2019	7
6	2/4/2019	7
7	2/17/2019	7
8	3/14/2019	9
9
10	sum between
11	1/2/2019	21
12	2/17/2019
Sheet1

Cell Formulas
Range	Formula
C11	=SUMPRODUCT((B4:B8>=B11)*(B4:B8<=B12)*C4:C8)

 

[Liberty Prime]

Hi Scott T,

thanks for the help.

The SUMPRODUCT does not work as its lacking a crosscheck among two different tabs and I can't make it work it I try to introduce this via = or MATCH.

And yes, the Google Spreadsheets I'm using do have the SUMIFS function, but again, I can't make them work. Here is what I have tried:

=SUMIFS('Input'!G:G,'Input'!A4:B750,A7&B7,'Input'!C4:C,'Input'!C4>='AdWords Input'!B2&'Input'!C4<=B2)

Input G:G is the column with the expenses
Input A:B are the columns with all the accounts
A7&B7 (from the output tab) are the exact accounts I want to be used as criteria for the sum
Input C:C is the column with all the dates
Lastly is the criteria of the date, that should be greater or equal to the first day of the month as also less or equal to the cell B2 (output tab), with the day to be checked.

I get the #VALUE error, but I have no clue what is wrong.

Best regards

 

[Liberty Prime]

Hum... Not sure if I'm not explaining myself very clear. I hope this will make it easier. With other words, what I would like to achieve is:

Check in the TAB Nº2 (input) the columns A:A&B:B&C:C if any of their combined rows match the combined cells A2&B2&C2 from TAB Nº1 (output). If true, then within this TAB Nº2 sum all the values of the column G:G for these matching rows.

 

[DanteAmor]

You can put some examples of what you have on sheet1 and on sheet2. In this example mark in some color which match and how much would be the sum.

 

[Scott T]

I have never used google sheets. Hopefully someone familiar with google sheets can help you.

 

[Liberty Prime]

After many attempts, I've finally found it


This is the cost per day: =INDEX('Input'!G:G,MATCH(A7&B7&(DATE(YEAR('Input'!$B$2),MONTH(DATEVALUE('Input'!$B$2)),$C$2)),'Input'!A:A&'Input'!B:B&'Input'!C:C,0))

This is the accumulated cost: =SUMIFS('Input'!G:G,'Input'!C:C,">="&MIN('Input'!C:C),'Input'!C:C,"<="&(DATE(YEAR('Input'!C:C),MONTH('Input'!C:C),$C$2)),'Input'!A:A,'Output'!A7,'Input'!B:B,'Output'!B7)


Maybe one last question. Instead of SUMIF and dividing by the amount of days to calculate the average cost per day, is there any AVERAGE formula to be used instead? Otherwise I won't complicate my life more than needed.

 

[DanteAmor]

Check this:

https://blog.sheetgo.com/google-sheets-formulas/averageifs-formula-google-sheets/