Count cells corresponding with quarter 1, 2, 3 and 4 without getting a #VALUE! error on blank cells.

[Squawk]

I'm at large here..
Must have spent more than a whole day searching the internet and trying all sorts of solutions.. to no result..

I try to make a spreadsheet that calculates what I've spent per yearly quarter, from a whole column of dates and spendings.
There are some blank cells in there as well. (Or cell's that contain a formula that returns a "blank" instead of a zero.)

This is the formulas I've tried:

=IF(SUMPRODUCT((ROUNDUP(MONTH(Expenses!B10:B305)/3;0)=1)*(Expenses!J10:J305))>0;SUMPRODUCT((ROUNDUP(MONTH(Expenses!B10:B305)/3;0)=1)*(Expenses!J10:J305));"")

B10:B305 contain the dates (from which I determine the year quarters).
J10:F305 are the actual expenses on those corresponding dates (which I want to sum up per quarter).

And it works, as long as there are no earlier mentioned blank cells in B10:B305 and J10:J305.

Other formulas I've tried, without result (they just return a "0") are:

=SUMIFS(Expenses!J10:J305;Expenses!B10:B305;ROUNDUP(MONTH(Expenses!B10:B305)/3;0)=1)

and

=SUMIF(Expenses!B10:B305;ROUNDUP(MONTH/3;0)=1;Expenses!J10:J305)

How can I make the first formula work with blanks in cells cells in B10:B305 and J10:J305?
Or.. what other way or formula can I use to get this done?

Any help is much appreciated!

Thanks

 

[wwilder]

Have you tried to use the IFERROR function in your formula?

 

[Squawk]

Thank you for your reply wwilder.
But that wouldn't work for me since the rest of the range still needs to be calculated. Plus there are no real errors in the cells.

It just needs to handle blank cells in the range specified by the SUMPRODUCT function..

 

[dreid1011]

Are you gathering the totals into a summary table? And you are just grouping them by date range?

 

[Squawk]

Just have 4 cell's named: Q1, Q2, Q3, Q4 that I try to let show the correct data.

 

[dreid1011]

Ok, this should work for you:

=SUM(SUMIFS($B$3:$B$32,$A$3:$A$32,">="&G$2,$A$3:$A$32,"<"&EDATE(G$2,3)),SUMIFS($C$3:$C$32,$A$3:$A$32,">="&G$2,$A$3:$A$32,"<"&EDATE(G$2,3)))

This is assuming you have 2 different columns you want to sum for each quarter. Also, I had to identify the start date of each quarter somewhere on the sheet in order to keep the formula short.

So, this is what I have set up:
(ignore column E)

Adjust ranges to suit your data.

 

[Squawk]

I played around with this method, but could not get it to work for me..
So I googled about your way and then I stumbled onto this link: teylyn.com/articles/excel-articles/sumproduct-error-messages/
and changed my formula to:
=IF(SUMPRODUCT(--(ROUNDUP(MONTH(Expenses!B10:B305)/3;0)=1);(Expenses!J10:J305))>0;SUMPRODUCT(--(ROUNDUP(MONTH(Expenses!B10:B305)/3;0)=1);(Expenses!J10:J305));"")

Now it works like a charm!

Thanks for the help guys!
Much appreciated.

 

[dreid1011]

You're welcome.