Re: monthly & quarterly Summery

Tech-Archive recommends: Fix windows errors by optimizing your registry

This is Perfect! Thank you very much Mr. Bob.

"Bob Phillips" wrote:

One bracket too many

=SUMPRODUCT(--($A$2:$A$13<>""),--(MONTH($A$2:$A$13)=H1),$B$2:$B$13)

--
__________________________________
HTH

Bob

"narnimar" <narnimar@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:4A5C5033-FB56-44BD-8478-7259F80377D0@xxxxxxxxxxxxxxxx
The answer for Question No. 1. is working perfect!
But your formula returns error message for my Question 2. The pop up with
mesage that "the formula you have entered has an error"

"Bob Phillips" wrote:



"narnimar" <narnimar@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:826E848C-A9F3-4459-994F-FF14F140D778@xxxxxxxxxxxxxxxx
Thanks. I get 1st quarter Sum using this formula. Now my question is -
1. how to get sum for 2nd 3rd and 4th Quarter?

=SUMPRODUCT(--(INT((MONTH($A$2:$A$13)+2)/3)=2),$B$2:$B$13)

or better, put the quarter number in a cell, say H1, and use

=SUMPRODUCT(--(INT((MONTH($A$2:$A$13)+2)/3)=H1),$B$2:$B$13)

etc.


2. how to get sum for each month?

put the month number in a acell and use

=SUMPRODUCT((--($A$2:$A$13<>""),--(MONTH($A$2:$A$13)=H1),$B$2:$B$13)

etc.






.

Relevant Pages

  • Re: combine CountA and CountIf
    ... (replace somewhere in email address with gmail if mailing direct) ... Bob Phillips wrote: ... Not sure what construction I need to avoid an error message. ... if the first formula produced 5 the second one would be ...
    (microsoft.public.excel.programming)
  • Re: hiding formula error #DIV/0!
    ... Bob Phillips ... (replace somewhere in email address with gmail if mailing direct) ... for a cell I use the formula: ... If there is no input yet in cells F4 or D4 an error message "#DIV/0!" ...
    (microsoft.public.excel)
  • Re: User message
    ... Dim rng As Range, x As Single ... Dim wsquote As Worksheet ... Bob Phillips ... ... How do i get the error message 'you have already deleted the> range' to appear if the range is no longer. ...
    (microsoft.public.excel.programming)
  • Re: Format(Time, "h.mm AM/PM") question
    ... And interestingly enough the error message ... "Compile error: expected function or variable" ... "Bob Phillips" wrote: ... items with MISSING in the text, ...
    (microsoft.public.excel.programming)
  • Re: Or operator
    ... but there was an error message. ... "Bob Phillips" wrote: ... > HTH ... >> format formula box, ...
    (microsoft.public.excel.worksheet.functions)