1. Registered User
Join Date
May 2002
Posts
157

Hi there,

I have the following data entered on an excel spreadsheet.

Column A Column B
Row 1 1 6
Row 2 1 150
Row 3 2 150
Row 4 3 0
Row 5 4 1100
Row 6 4 150
Row 7 5 0
Row 8 5 390
Row 9 5 1100
Row 10 6 0
Row 11 6 0
Row 12 6 150
Row 13 7 0
Row 14 7 0
Row 15 7 150
Row 16 8 0
Row 17 9 0
Row 18 11 150
Row 19 12 150
Row 20 13 390
Row 21 14 0
Row 22 15 0
Row 23 25 390
Row 24 Ass 0
Row 25 Dec 0
Row 26 Did 0
Row 27 Did 0
Row 28 Ear 0
Row 29 Exi 0
Row 30 Ful 0
Row 31 Men 0
Row 32 Men 0
Row 33 P/T 0
Row 34 Pre 0
Row 35 REF 310
Row 36 REF 310
Row 37 REF 150
Row 38 REF 150
Row 39 Ref 0
Row 40 Rel 0
Row 41 Tra 0
Row 42
Row 43
Row 44
Row 45
Row 46

I have two columns.

Column A has blank cells, alpha words, and numerics
Column B has blank cells and numerics in each cell

I want to count the number of cells in Column B which has 390 entered into it as long as column A has a numeric in the cell.
ie if there is alpha text or a blank cell in column A, then I do not want the equivalent cell (same position in the array) of Column B to be counted
The following is near but not close enough
=SUMPRODUCT((B2:B47>=1)*(C2:C47>=0)) gives the answer of 46, which includes blank cells and alpha cells

Thankyou in advance for any assistance given.

Regards

Karen Day
Last edited by Karen Day; 07-03-08 at 04:07. Reason: not clearly showing on forum

2. Registered User
Join Date
Apr 2004
Location
Derbyshire, UK
Posts
805
Hi

From your description I think this might do it

=SUMPRODUCT(ISNUMBER(B2:B47)*(C2:C47=390))

??

If not, then I may have misinterpreted you problem!?

MTB

3. Registered User
Join Date
May 2002
Posts
157

## Thank you

It works perfectly - thank you so much
Regards
Karen

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•