Simple Dfunction

  • Thread starter Thread starter For example John Smith
  • Start date Start date
F

For example John Smith

I have a simple range set as follows:-

C5:I20

Row C5:I5 contains the headings, AdType, Month1, Month2 etc
Column C: contains repeated descriptive text, Half Page, Full Page,
Editorial etc.

Range D6:I20 contains the month names, Sep05, Oct05, etc in any order but
can not be duplicated on the same row.

I simply want to find how may half pages we have sold in Sep05, Full pages
etc etc,

I have tried various functions such as vlookup, DcountA, countif, all
functions i currently use succesfully, but this little one has flawed me.

Any suggestions

Thanks
 
Hi!

Since no month name will be duplicated on any row:

=SUMPRODUCT((C6:C20="Half Page")*(D6:I20="Sep05"))

Better:

A1 = Half Page
B1 = Sep05

=SUMPRODUCT((C6:C20=A1)*(D6:I20=B1))

Biff
 
Biff

You are an absolute star, thanks very much.

Just one question if there is no data in some of the fields in either of the
ranges it does not calculate (#N/A) is there a way around this ?

Thanks again

Rich
 
Hi!
Just one question if there is no data in some of the fields in either of
the ranges it does not calculate (#N/A) is there a way around this ?
Click to expand...

Not sure what you mean by this. The only way the formula will return #N/A is
if there are logical #N/A's in either range.

If that's the case, try this:

=SUMPRODUCT(--(ISNUMBER(SEARCH("half
page",C6:C20)))*(ISNUMBER(SEARCH("sep05",D6:I20))))

Biff
 
Back
Top