• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Quartely Totals Unsing SUMPRODUCT

Starting with the sales formula:
=SUMPRODUCT((ROUNDUP(MONTH($B$4:$B$39)/3,0)=ROWS($E$5:E5))*($C$4:$C$39)*(YEAR($B$4:$B$39)=F$4))
The portion in Blue is what we want to change. Assuming that range has the L's and O's, would be:
=SUMPRODUCT((ROUNDUP(MONTH($B$4:$B$39)/3,0)=ROWS($E$5:E5))*(($C$4:$C$39="L")+($C$4:$C$39="O")>0)*(YEAR($B$4:$B$39)=F$4))

We add the criteria ranges together since this is an "OR" type logic check.
 
Luke M said:
Starting with the sales formula:
=SUMPRODUCT((ROUNDUP(MONTH($B$4:$B$39)/3,0)=ROWS($E$5:E5))*($C$4:$C$39)*(YEAR($B$4:$B$39)=F$4))
The portion in Blue is what we want to change. Assuming that range has the L's and O's, would be:
=SUMPRODUCT((ROUNDUP(MONTH($B$4:$B$39)/3,0)=ROWS($E$5:E5))*(($C$4:$C$39="L")+($C$4:$C$39="O")>0)*(YEAR($B$4:$B$39)=F$4))

We add the criteria ranges together since this is an "OR" type logic check.
Click to expand...
Awesome Luke!

I was trying to use COUNTIF. Now I see.

Thanks a bunch!
 
@Luke M
Hi!
Gotcha! At last... but yeah! I now know what does the M after Luke stands for... you unveiled the secret!
Monet.
Regards!
 
You must log in or register to reply here.
Back
Top