PDA

View Full Version : search and count values



arnab0711
05-23-2012, 02:46 AM
Hi,
I want a count formula in "Snap shot" sheet.
The formula will be based on "details" sheet and the criteria will be--
to count all distributors billed/not billed where month = April or May , Model type will be
64 KB Prepaid sim-NANO-MNP
Pre Paid 32K nano sim
RCV 10
RCV 20
RCV 35
RCV10-30 mins local
STV25-75 local mins
FTV10-25 Local-STd
** any thing that has a value in details sheet is billed and others(0) is unbilled.

Bob Phillips
05-23-2012, 04:10 AM
Where is the region in the data on RawSheet?

arnab0711
05-23-2012, 04:21 AM
Column J (zones)

arnab0711
05-23-2012, 10:03 AM
but how is relevent here

Bob Phillips
05-23-2012, 12:17 PM
You could use these formulas, But I get very different numbers to you

=SUMPRODUCT((Rawsheet!$J$5:$J$1000=TRIM($B$1))*(Rawsheet!$E$5:$E$1000=TRIM( $C$2))*(Rawsheet!$F$5:$F$1000=B4)*(Rawsheet!$H$5:$H$1000<>0))

and

=SUMPRODUCT((Rawsheet!$J$5:$J$1000=TRIM($B$1))*(Rawsheet!$E$5:$E$1000=TRIM( $C$2))*(Rawsheet!$F$5:$F$1000=B4)*(Rawsheet!$H$5:$H$1000=0))

arnab0711
05-28-2012, 02:42 AM
Hi Vbax,
Pl advice,if I can post my query in this post ?

Bob Phillips
05-28-2012, 03:24 AM
Yes, click the 'Go Advanced' button, there is a 'Manage Attachments' button within there.

arnab0711
05-28-2012, 05:11 AM
Hi,
I have made removed the leading spaces,and unmarged the cells,and then tried to use your formula by tweaking it,but unable to get result,I am attaching the formula sheet,please advice if you need any other information.

Bob Phillips
05-28-2012, 05:52 AM
You should be using

=SUMPRODUCT((Rawsheet!$F$5:$F$1000=$B5)*(Rawsheet!$J$5:$J$1000=$C$2)*(Rawsh eet!$E$5:$E$1000=C$3)*(Rawsheet!$H$5:$H$1000<>0))

and

=SUMPRODUCT((Rawsheet!$F$5:$F$1000=$B5)*(Rawsheet!$J$5:$J$1000=$C$2)*(Rawsh eet!$E$5:$E$1000=C$3)*(Rawsheet!$H$5:$H$1000=0))

arnab0711
05-28-2012, 06:07 AM
Hi,
I am attaching another sheet,where the same thing was done using a pivot table,now the data of this sheet and your formula should have matched but currently it not matching

Bob Phillips
05-28-2012, 06:36 AM
The month April in your tables has a trailing space.

arnab0711
05-28-2012, 09:57 AM
Hi XLD,
There is a slight change which I forgot to mention,that is all distributor codes in column 'I' in a particular zone,month,model type should be treated as 1
for example distributor code "44745" has 3 occourences,but 3 codes will be treated as 1.

arnab0711
05-29-2012, 03:36 AM
Hi Xld,
Pl advice,if I am clear with my query.