Count Non blanks cells form column based on month in another column and sum the values of cells?

1
votes

I have a spread sheet with two columns - first is date column and second is amount column. Now I am creating another two columns with name "No. of Transactions Based on Month" and "Total Amount Based on Month" respectively. Some cells of amount column are blank. Currently my sheet is like this:

enter image description here

Now, I am trying to add formulas which will automatically calculate number of transactions and total amount with respect to months.

My current formula to get No. of transactions are =SUMPRODUCT(--(Month(A2:A7)=1)) for January and =SUMPRODUCT(--(Month(A2:A7)=4)) for April. The problem with this approach is that they return 3 and 3 transactions whereas they should return 2 and 2 because 1 transaction is empty.

Likewise, the formula I am using to get total amount per month is =SUM(B2:B4) for January and =SUM(B5:B7) for April. The problem here is if we have 1000 transactions in every month then it will become headache to select cells. So I need formula which will automatically sum based on month.

What are the best approaches?

2
excelexcel-formula

2 Answers

1
votes

Adjust you SUMPRODUCT to include a check for blanks:

=SUMPRODUCT((Month(A2:A7)=1)*(B2:B7<>""))

Then for the sum:

=SUMPRODUCT((Month(A2:A7)=1)*B2:B7)
0
votes

=SUMPRODUCT(--(MONTH(A2:A7)=1),--(B2:B7>0)) should fix this for you. All you needed was the additional column. The beauty of sumproduct is that it allows you to multiply across columns and add the results.

By the way, if you want to automate the month portion of the formula, you could use MONTH(D2&" 1, 1900"), which basically turns your "April" into "April 1, 1900" in order to allow you to call the MONTH function.

That would leave you with the draggable formula =SUMPRODUCT(--(MONTH(A$2:A$7)=MONTH(D2&" 1, 1900")),--(B$2:B$7>0)).