• 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.

Counting No of Rows with Non Zero Totals over non contiguous columns dynamically

B

bhutaniv

New Member
Hi Chandoo!

I have a problem….I have an Excel 2007 Sheet with Name of Dealers in Rows and Month wise Sales for the Financial Year in Columns (Apr.10, Apr.11, May.10,May.11….Mar.11,Mar.12). I have a Dashboard Sheet where the user enters the starting month & ending month for e.g. Apr.11 TO Sep.11.

I want to calculate the No of Dealers having registered some sales in the period i.e. Apr.11+May.11+…..Sep.11 should not be Zero using a formula. This needs to be calculated dynamically depending on the value entered in the Dashboard Month Field.
 
Bhutaniv


Firstly, Welcome to the Chandoo.org forums


Your problem sounds like a classic use of Sumproduct


Assuming that Column A has the Dealers

The dates are in Row 1 and are true dates, not text

and that the data area is B2:Y6

B10 has the Dealers Name

B8 has the from Date

B9 has the to Date


Formulas like:


Sum of Sales per Salesman

Code: 
=SUMPRODUCT((A2:A6=B10)*(B1:Y1>=B8)*(B1:Y1<=B9)*(B2:Y6))

Will do the job

Adjust the ranges to suit


[b]Total Sales over the period[/b]

[code]=SUMPRODUCT((B1:Y1>=B8)*(B1:Y1<=B9)*(B2:Y6))


No Of sales months over the Period

=SUMPRODUCT((B1:Y1>=B8)*(B1:Y1<=B9)*(B2:Y6>0))[/code]


You can read more about how this works here:

http://chandoo.org/wp/2011/05/26/advanced-sumproduct-queries/
 
Bhutaniv


To answer your original question Sam an ExcelHero MVP has came up with a solution to the original question that I skirted around

It involves a series of Named Formula and is very clever but rather difficult to explain so I have uploaded a copy here:

https://rapidshare.com/files/2618229288/Sales_Solution.xlsx
 
Bhutaniv


The following Array Formula was supplied by Oleksiy a Student at Excel Hero Academy

It must be used in conjunction with the file I uploaded above as it uses a few Named Ranges already in that file:

Code: 
=SUM((FREQUENCY((Periods>=From)*(Periods<=To)*(Data>0)*(ROW(Dealer)-1), ROW( OFFSET( Dealer, 0,0, ROWS(Dealer) +1)) -2) >0)*(( ROW( OFFSET(Dealer,0,0, ROWS(Dealer)+2))-2)>0))
Ctrl Shift Enter
 
Bhutaniv


Koitaki respoded over at the Excel Hero Academy with this beauty

=SUM(--(MMULT((B1:Y1>=From)*(B1:Y1<=To)*(Data<>0), TRANSPOSE(B1:Y1=B1:Y1)*1)<>0)) Ctrl Shift Enter
 
You must log in or register to reply here.
Back
Top