help with sum / count formula

[Fiona Yorke-Saville]

Hi,

Does anyone know how I can write the following formula...

I have different references in coloumn B and random cells filled in from
coloumns C-F.

I want the formula to count how many cells have data in them from C1 - F100,
but only if coloumn B contains the reference in A1.

eg

A B C D E F
1 ZZZ ZXY aaa acd
2 ZZZ abc aaa
3 YYY aaa

I would want the formula to show 2 from the above data, as there are 2 cells
filled in, where coloumn B = A1.

I wasn't sure if it's a sum or count formula, any help would be great.

Thanks

 

[NBVC]

Try:

=SUMPRODUCT((B1:B3=A1)*(C1:F3<>"")

--
NBV

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com

 

[Fiona Yorke-Saville]

Thanks, this seems to multiply the number in coloumn B by the number in
coloumn C-F.

Would it be possible to just count any cells that are filled in, in coloumns
C-F if coloumn B = A1?

 

[Eduardo]

Hi,
the formula given count, the * doesn't mean in this case that multiply the
values, is just used by sumproduct to count

 

[Glenn]

But what does it actually do?

Thanks, this seems to multiply the number in coloumn B by the number in
coloumn C-F.

Would it be possible to just count any cells that are filled in, in coloumns
C-F if coloumn B = A1?

 

[Fiona]

Thanks Eduardo,

The result the formula gives is a lot more than it should be. I'm not sure
why?
The cells I'm looking at only have 4 items filled in on coloumn C, but it's
giving me a result of 12?

 

[Sean Timmons]

I got 2. Did you copy and paste from here? May be getting null value cells
in there...

 

[Sean Timmons]

It says where the cells in column B = A1, count the cells in coluns C through
F that are not null.

this is an array formula that assigns 0's and 1's based on True/False, then
adds them all up.

 

[Glenn]

Personally, I understand what the formula does. My question was for the OP, who
apparently didn't try it before stating her opinion of what it "seems" to do.

Sorry for the lack of clarity.

 

[Eduardo]

Hi Fiona,
I teste NBVC formula and is working for me, check that you really has
nothing in the cells without information, maybe you have a space that will
change your count, formula look for cells that are not empty

 

[NBVC]

Thanks, this seems to multiply the number in coloumn B by the number in
coloumn C-F.

Would it be possible to just count any cells that are filled in, i
coloumns
C-F if coloumn B = A1?



Forums' (http://www.thecodecage.com/forumz/showthread.php?t=138950)

The formula given says to count each occurance of a non-blank cel
within C1:F3 where corresponding item in column B (B1:B3) matches th
value in A1.

With the samply you gave, I got 2 as you required...

The * in Sumproduct, means AND.

--
NBV

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com

 

[Sean Timmons]

Sure thing, but since no one else had done it, my explanation may help the OP
anyways....