Nested Countif

**tuck80** · 02-01-2007, 05:26 PM

I am having a real problem and have looked everywhere? I have a speadsheet 7 colums and 50 rows, in colum B it has either a W or L in it and in colum G it either has C or F in it In a nother cell I need to display as a number if a row contains a W in colum B and a C in colum G?

Can anyone please help with this???

Thanks

**VBA Noob** · 02-01-2007, 05:31 PM

Try

=SUMPRODUCT(--(B1:B200="W"),--(G1:G200="C"))

VBA Noob

**tuck80** · 02-01-2007, 05:36 PM

when I paste this into excel it just displays

=SUMPRODUCT(--(B1:B200="W"),--(G1:G200="C")) in the cell

not the result, am I doing something wrong?

**VBA Noob** · 02-01-2007, 05:38 PM

Hi,

1). Make sure there is no leading space before the equals sign
2). Make sure the cell is formatted as general not text

VBA Noob

**tuck80** · 02-01-2007, 05:41 PM

THANK YOU, its working great! I put a space before the formula

Thank you

**VBA Noob** · 02-01-2007, 05:42 PM

No problem

Glad it helped

VBA Noob

**tuck80** · 02-01-2007, 05:59 PM

Works great on my computer but when I put it in my pda it does not seem to work? is the another way this can be done? that my work on my pda

**VBA Noob** · 02-01-2007, 07:01 PM

Hopefully you can use an array CSE.

Enter with Ctrl + Shift + Enter

=SUM((B1:B50="w")*(G1:G50="C")*1)

VBA Noob

**tuck80** · 02-01-2007, 07:02 PM

i am not sure how to enter that way on pocket excel

**VBA Noob** · 02-01-2007, 07:13 PM

Can think of a way at the moment

Could you refer to the sumproduct formula

E.g Say Sumproduct in H1 then in J1 enter = H1

For your ref these are the functions you can use for a PDA

http://www.ehansberry.com/pages/Pock...lFunctions.htm

VBA Noob

**VBA Noob** · 02-01-2007, 07:26 PM

Or

use a helper column. So enter this a column after your data e.g Col 8 (I)

=IF(AND(B1="W",G1="C"),1,0)

and drag down. Then sum the 1's

VBA Noob

**tuck80** · 02-01-2007, 07:30 PM

So put this in the 8th colum =IF(AND(B1="W",G1="C"),1,0)

What do I put in the cell were I want to display the result of number of rows with a W and a C?

**VBA Noob** · 02-01-2007, 07:37 PM

It's a helper column.

When you drag the formula down it will show 1's and O's

The 1's are rows which have a W in Col B and C in Col G.

Then use a simple sum formula to sum the nuber 1's in I in say J1 e.g = Sum(I:I) and you can then hide Column I

VBA Noob

**tuck80** · 02-01-2007, 07:44 PM

just waiting for my pda to get some charge and I will try it and post my results

Nested Countif

LinkBack

Thread Tools

Rate This Thread

Display

Nested Countif

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions