CODEWITHSUNDEEP
google-sheets-formula
user209336
user209336

Reputation: 25

Small detail when using this function in Google Sheets

Small detail when using =INDEX($A$8:$A$11;MATCH(MAX(SUMIF($B$8:$B$11;$B$8:$B$11));SUMIF($B$8:$B$11;$B$8:$B$11);0))) If the values in column B are all different it returns the correct date value, but if two identical values in column B coincide in different dates then it returns the date of the first value; it does not return the correct date and it keeps the first one that has the repeated value. Any idea?

enter image description here

p.s This question can be added to this post

Upvotes: 0

Views: 46

Answers (2)

Osm
Osm

Reputation: 2881

Even more easier way:
On E2 Try this =TRANSPOSE(INDEX(QUERY(A1:B," select A, sum(B) group by A Order By sum(B) Desc "),2))
and format the date and currency accordingly.

Upvotes: 1

Osm
Osm

Reputation: 2881

You can do that easily and differently to get:

1 - Make a helper table to get unique dates, You can use two ways

a) - Use SUMIF Function to get the sum of Expenditure in each unique date Like so =IF(D2="",,SUMIF($A$2:$A,D2,$B$2:$B)) and drag it down.

b) - By using QUERY Function =QUERY(A1:B11," select A, sum(B) group by A Order By sum(B) Desc ")

2 - to get SUM BY DATE OF HIGHEST EXPENDITURE: =MAX(E2:E)

3 - to get DATE BY HIGHEST EXPENDITURE: =INDEX($D$2:$D,MATCH($H$3,$E$2:$E,0),1)

Make a copy of this sheet "make it yours."
Hope that answerd your question.

Upvotes: 0

Related Questions