Simple Dfunction

  • Thread starter For example John Smith
  • 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
 
B

Biff

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
 
F

For example John Smith

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
 
B

Biff

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
 
Top