Search code examples
google-sheetsgoogle-sheets-formulagoogle-sheets-query

How can you sum up the amount of items in a month?

enter image description hereI have a data set that records in each row a particular item and the day it was entered. In this example set, I want to figure out how to sum the number of bananas and oranges that occur in a particular month. I am able to write a formula to count the total amount of entries in a month, but I am stuck on how to do that per month, and then per item. The cells highlighted yellow are where I'm stuck.

To find the total amount of entries per month, I have used (for example in cell F4):

=SUMPRODUCT((MONTH(A4:A21) = D4)*(YEAR(A4:A21) = B1))

To find the total amount of each type of fruit, I have tried:

To find the total amount of a particular fruit in a particular month I have tried (for example, in cell H4):

=IF(SUMPRODUCT(1*(MONTH(A4:A21)=D4)), COUNTIF(B4:B21, "Bananas"), "")

But my problem is that this is just summing the total amount of Bananas across all months, it's not specifically for a single month.

Solution 
  • =QUERY(QUERY(A$4:B, "where month (A)+1="&MONTH(E4&1)), 
 "select Col2, count(Col2) group by Col2 label count(Col2)''")

    0