Sumproduct with text
 

Hi all

I am using Excel 2000

I am wanting to use the sumproduct function or something similar to resolve
the following problem.

I have a table with one product per column (colums C:H)
In Column A I have dates
In row 4 I have the selling price of each product

In the rest of the table I have client names showing which client bought
which product on which date.

What I would like to do is use the Sumproduct function or something similar
to place the total sales value on a particular day into column 'B'.

The client never buys more than one item so it can be assumed that if a cell
value is text (the clients name) then it is equal to 1 but of course there
may be more than one product sold on a particular date.

I suppose another way to explain this is that I want to use Sumproduct but
one of the sets of data is not a value but is text and unless the cell is
blank in this set of data its value is 1.

Any ideas or a solutions would be appreciated

George

Sumproduct with text
 

It sounds like you are going to have to nest a countif function with th
sumproduct

--
Message posted from http://www.ExcelForum.com

Sumproduct with text
 

Hi
maybe something like
=SUMPRODUCT(--(C5:H5<""))

--
Regards
Frank Kabel
Frankfurt, Germany


George Andrews wrote:
Hi all

I am using Excel 2000

I am wanting to use the sumproduct function or something similar to
resolve the following problem.

I have a table with one product per column (colums C:H)
In Column A I have dates
In row 4 I have the selling price of each product

In the rest of the table I have client names showing which client
bought which product on which date.

What I would like to do is use the Sumproduct function or something
similar to place the total sales value on a particular day into
column 'B'.

The client never buys more than one item so it can be assumed that if
a cell value is text (the clients name) then it is equal to 1 but of
course there may be more than one product sold on a particular date.

I suppose another way to explain this is that I want to use
Sumproduct but one of the sets of data is not a value but is text and
unless the cell is blank in this set of data its value is 1.

Any ideas or a solutions would be appreciated

George

Sumproduct with text
 

To get the value, use

=SUMPRODUCT((C5:H5<"")*C$4:H$4)

and copy down for each subsequent row of client data

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Frank Kabel" wrote in message
...
Hi
maybe something like
=SUMPRODUCT(--(C5:H5<""))

--
Regards
Frank Kabel
Frankfurt, Germany


George Andrews wrote:
Hi all

I am using Excel 2000

I am wanting to use the sumproduct function or something similar to
resolve the following problem.

I have a table with one product per column (colums C:H)
In Column A I have dates
In row 4 I have the selling price of each product

In the rest of the table I have client names showing which client
bought which product on which date.

What I would like to do is use the Sumproduct function or something
similar to place the total sales value on a particular day into
column 'B'.

The client never buys more than one item so it can be assumed that if
a cell value is text (the clients name) then it is equal to 1 but of
course there may be more than one product sold on a particular date.

I suppose another way to explain this is that I want to use
Sumproduct but one of the sets of data is not a value but is text and
unless the cell is blank in this set of data its value is 1.

Any ideas or a solutions would be appreciated

George

Sumproduct with text
 

Thank you all for you help.

I have solved the problem for now.

George


"Bob Phillips" wrote in message
...
To get the value, use

=SUMPRODUCT((C5:H5<"")*C$4:H$4)

and copy down for each subsequent row of client data

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Frank Kabel" wrote in message
...
Hi
maybe something like
=SUMPRODUCT(--(C5:H5<""))

--
Regards
Frank Kabel
Frankfurt, Germany


George Andrews wrote:
Hi all

I am using Excel 2000

I am wanting to use the sumproduct function or something similar to
resolve the following problem.

I have a table with one product per column (colums C:H)
In Column A I have dates
In row 4 I have the selling price of each product

In the rest of the table I have client names showing which client
bought which product on which date.

What I would like to do is use the Sumproduct function or something
similar to place the total sales value on a particular day into
column 'B'.

The client never buys more than one item so it can be assumed that if
a cell value is text (the clients name) then it is equal to 1 but of
course there may be more than one product sold on a particular date.

I suppose another way to explain this is that I want to use
Sumproduct but one of the sets of data is not a value but is text and
unless the cell is blank in this set of data its value is 1.

Any ideas or a solutions would be appreciated

George