SumIf
 

Can anybody please tell me how I extract data that matches the following:
Need to extract the sum of sales that are in a column that matches certain
criteria.
I have a column with products that are sold by certain companys. Some sell
pens, inks, staples etc. Different companys sell the same products.
I need all the pens and all the staples that are sold by a certain company.
Or maybe two companys that sell pens and paper.
Perhaps ifsum(d2:d150 need pens and paper (maybe from 2 different companys)
if match add the total values from them.
I hope I have made myself clear!!!
Thank you all.
Regards.
S

SumIf
 

If col A has the company names, col B has the products, and col C has the
sales volumes, then

=SUMPRODUCT(--(A1:A1000="Swingline"),--(B1:B1000= "Staples"),C1:C1000)

"Sal" wrote:

Can anybody please tell me how I extract data that matches the following:
Need to extract the sum of sales that are in a column that matches certain
criteria.
I have a column with products that are sold by certain companys. Some sell
pens, inks, staples etc. Different companys sell the same products.
I need all the pens and all the staples that are sold by a certain company.
Or maybe two companys that sell pens and paper.
Perhaps ifsum(d2:d150 need pens and paper (maybe from 2 different companys)
if match add the total values from them.
I hope I have made myself clear!!!
Thank you all.
Regards.
S

SumIf
 

Hi and thank you for such a quick response.
What if I need 2 products in the column B, and sometimes 2 products from 2
different companys in Column A?
S

"Duke Carey" wrote:

If col A has the company names, col B has the products, and col C has the
sales volumes, then

=SUMPRODUCT(--(A1:A1000="Swingline"),--(B1:B1000= "Staples"),C1:C1000)

"Sal" wrote:

Can anybody please tell me how I extract data that matches the following:
Need to extract the sum of sales that are in a column that matches certain
criteria.
I have a column with products that are sold by certain companys. Some sell
pens, inks, staples etc. Different companys sell the same products.
I need all the pens and all the staples that are sold by a certain company.
Or maybe two companys that sell pens and paper.
Perhaps ifsum(d2:d150 need pens and paper (maybe from 2 different companys)
if match add the total values from them.
I hope I have made myself clear!!!
Thank you all.
Regards.
S

SumIf
 

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A1000,{"Swingline","WalMart"},0 ))),--(ISNUM
BER(MATCH(B1:B1000,{"Staples","Pens"},0))),C1:C100 0)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Sal" wrote in message
...
Hi and thank you for such a quick response.
What if I need 2 products in the column B, and sometimes 2 products from 2
different companys in Column A?
S

"Duke Carey" wrote:

If col A has the company names, col B has the products, and col C has
the
sales volumes, then

=SUMPRODUCT(--(A1:A1000="Swingline"),--(B1:B1000= "Staples"),C1:C1000)

"Sal" wrote:

Can anybody please tell me how I extract data that matches the
following:
Need to extract the sum of sales that are in a column that matches
certain
criteria.
I have a column with products that are sold by certain companys. Some
sell
pens, inks, staples etc. Different companys sell the same products.
I need all the pens and all the staples that are sold by a certain
company.
Or maybe two companys that sell pens and paper.
Perhaps ifsum(d2:d150 need pens and paper (maybe from 2 different
companys)
if match add the total values from them.
I hope I have made myself clear!!!
Thank you all.
Regards.
S

SumIf
 

Hi Bob
I tried the following but it returned nothing (0) despite values being
there? Any help?
=SUMPRODUCT(--(ISNUMBER(MATCH(A4:A31,{"Advertising Concepts","Union
News"},0))),--(ISNUMBER(MATCH(C4:C31,{"Other","Pens"

S

"Bob Phillips" wrote:

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A1000,{"Swingline","WalMart"},0 ))),--(ISNUM
BER(MATCH(B1:B1000,{"Staples","Pens"},0))),C1:C100 0)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Sal" wrote in message
...
Hi and thank you for such a quick response.
What if I need 2 products in the column B, and sometimes 2 products from 2
different companys in Column A?
S

"Duke Carey" wrote:

If col A has the company names, col B has the products, and col C has
the
sales volumes, then

=SUMPRODUCT(--(A1:A1000="Swingline"),--(B1:B1000= "Staples"),C1:C1000)

"Sal" wrote:

Can anybody please tell me how I extract data that matches the
following:
Need to extract the sum of sales that are in a column that matches
certain
criteria.
I have a column with products that are sold by certain companys. Some
sell
pens, inks, staples etc. Different companys sell the same products.
I need all the pens and all the staples that are sold by a certain
company.
Or maybe two companys that sell pens and paper.
Perhaps ifsum(d2:d150 need pens and paper (maybe from 2 different
companys)
if match add the total values from them.
I hope I have made myself clear!!!
Thank you all.
Regards.
S

SumIf
 

Sorry this:
=SUMPRODUCT(--(ISNUMBER(MATCH(A4:A31,{"Advertising Concepts","Union
News"},0))),--(ISNUMBER(MATCH(C4:C31,{"Other","Pens"},0))),I4:I3 1)

"Bob Phillips" wrote:

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A1000,{"Swingline","WalMart"},0 ))),--(ISNUM
BER(MATCH(B1:B1000,{"Staples","Pens"},0))),C1:C100 0)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Sal" wrote in message
...
Hi and thank you for such a quick response.
What if I need 2 products in the column B, and sometimes 2 products from 2
different companys in Column A?
S

"Duke Carey" wrote:

If col A has the company names, col B has the products, and col C has
the
sales volumes, then

=SUMPRODUCT(--(A1:A1000="Swingline"),--(B1:B1000= "Staples"),C1:C1000)

"Sal" wrote:

Can anybody please tell me how I extract data that matches the
following:
Need to extract the sum of sales that are in a column that matches
certain
criteria.
I have a column with products that are sold by certain companys. Some
sell
pens, inks, staples etc. Different companys sell the same products.
I need all the pens and all the staples that are sold by a certain
company.
Or maybe two companys that sell pens and paper.
Perhaps ifsum(d2:d150 need pens and paper (maybe from 2 different
companys)
if match add the total values from them.
I hope I have made myself clear!!!
Thank you all.
Regards.
S

SumIf
 

Sal -

So long as the text values in the cells match your terms exactly, this works
just fine for me. If I add a space at the end or the beginning of each, the
formula returns zero.

Make sure that all of your text values have no leading or trailing spaces &
see what happens


"Sal" wrote:

Sorry this:
=SUMPRODUCT(--(ISNUMBER(MATCH(A4:A31,{"Advertising Concepts","Union
News"},0))),--(ISNUMBER(MATCH(C4:C31,{"Other","Pens"},0))),I4:I3 1)

"Bob Phillips" wrote:

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A1000,{"Swingline","WalMart"},0 ))),--(ISNUM
BER(MATCH(B1:B1000,{"Staples","Pens"},0))),C1:C100 0)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Sal" wrote in message
...
Hi and thank you for such a quick response.
What if I need 2 products in the column B, and sometimes 2 products from 2
different companys in Column A?
S

"Duke Carey" wrote:

If col A has the company names, col B has the products, and col C has
the
sales volumes, then

=SUMPRODUCT(--(A1:A1000="Swingline"),--(B1:B1000= "Staples"),C1:C1000)

"Sal" wrote:

Can anybody please tell me how I extract data that matches the
following:
Need to extract the sum of sales that are in a column that matches
certain
criteria.
I have a column with products that are sold by certain companys. Some
sell
pens, inks, staples etc. Different companys sell the same products.
I need all the pens and all the staples that are sold by a certain
company.
Or maybe two companys that sell pens and paper.
Perhaps ifsum(d2:d150 need pens and paper (maybe from 2 different
companys)
if match add the total values from them.
I hope I have made myself clear!!!
Thank you all.
Regards.
S

SumIf
 

Hi Duke
Still having problems - can I send you the worksheet details please.
If I can, please tell me how I attach it for you.
Much appreciated. Sal

"Duke Carey" wrote:

Sal -

So long as the text values in the cells match your terms exactly, this works
just fine for me. If I add a space at the end or the beginning of each, the
formula returns zero.

Make sure that all of your text values have no leading or trailing spaces &
see what happens


"Sal" wrote:

Sorry this:
=SUMPRODUCT(--(ISNUMBER(MATCH(A4:A31,{"Advertising Concepts","Union
News"},0))),--(ISNUMBER(MATCH(C4:C31,{"Other","Pens"},0))),I4:I3 1)

"Bob Phillips" wrote:

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A1000,{"Swingline","WalMart"},0 ))),--(ISNUM
BER(MATCH(B1:B1000,{"Staples","Pens"},0))),C1:C100 0)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Sal" wrote in message
...
Hi and thank you for such a quick response.
What if I need 2 products in the column B, and sometimes 2 products from 2
different companys in Column A?
S

"Duke Carey" wrote:

If col A has the company names, col B has the products, and col C has
the
sales volumes, then

=SUMPRODUCT(--(A1:A1000="Swingline"),--(B1:B1000= "Staples"),C1:C1000)

"Sal" wrote:

Can anybody please tell me how I extract data that matches the
following:
Need to extract the sum of sales that are in a column that matches
certain
criteria.
I have a column with products that are sold by certain companys. Some
sell
pens, inks, staples etc. Different companys sell the same products.
I need all the pens and all the staples that are sold by a certain
company.
Or maybe two companys that sell pens and paper.
Perhaps ifsum(d2:d150 need pens and paper (maybe from 2 different
companys)
if match add the total values from them.
I hope I have made myself clear!!!
Thank you all.
Regards.
S

SumIf
 

Sal -

It wouldn't make it past the firewall here.

Another problem might be that the numbers you are summing are actually seen
by Excel as text. Try copying a blank/empty cell, then selecting all the
numbers & using EditPaste SpecialAdd. That will force the numbers from
text to numeric values.



"Sal" wrote:

Hi Duke
Still having problems - can I send you the worksheet details please.
If I can, please tell me how I attach it for you.
Much appreciated. Sal

"Duke Carey" wrote:

Sal -

So long as the text values in the cells match your terms exactly, this works
just fine for me. If I add a space at the end or the beginning of each, the
formula returns zero.

Make sure that all of your text values have no leading or trailing spaces &
see what happens


"Sal" wrote:

Sorry this:
=SUMPRODUCT(--(ISNUMBER(MATCH(A4:A31,{"Advertising Concepts","Union
News"},0))),--(ISNUMBER(MATCH(C4:C31,{"Other","Pens"},0))),I4:I3 1)

"Bob Phillips" wrote:

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A1000,{"Swingline","WalMart"},0 ))),--(ISNUM
BER(MATCH(B1:B1000,{"Staples","Pens"},0))),C1:C100 0)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Sal" wrote in message
...
Hi and thank you for such a quick response.
What if I need 2 products in the column B, and sometimes 2 products from 2
different companys in Column A?
S

"Duke Carey" wrote:

If col A has the company names, col B has the products, and col C has
the
sales volumes, then

=SUMPRODUCT(--(A1:A1000="Swingline"),--(B1:B1000= "Staples"),C1:C1000)

"Sal" wrote:

Can anybody please tell me how I extract data that matches the
following:
Need to extract the sum of sales that are in a column that matches
certain
criteria.
I have a column with products that are sold by certain companys. Some
sell
pens, inks, staples etc. Different companys sell the same products.
I need all the pens and all the staples that are sold by a certain
company.
Or maybe two companys that sell pens and paper.
Perhaps ifsum(d2:d150 need pens and paper (maybe from 2 different
companys)
if match add the total values from them.
I hope I have made myself clear!!!
Thank you all.
Regards.
S

SumIf
 

Hi
Nope tried that to no avail - going bald here!!!
Sal

"Duke Carey" wrote:

Sal -

It wouldn't make it past the firewall here.

Another problem might be that the numbers you are summing are actually seen
by Excel as text. Try copying a blank/empty cell, then selecting all the
numbers & using EditPaste SpecialAdd. That will force the numbers from
text to numeric values.



"Sal" wrote:

Hi Duke
Still having problems - can I send you the worksheet details please.
If I can, please tell me how I attach it for you.
Much appreciated. Sal

"Duke Carey" wrote:

Sal -

So long as the text values in the cells match your terms exactly, this works
just fine for me. If I add a space at the end or the beginning of each, the
formula returns zero.

Make sure that all of your text values have no leading or trailing spaces &
see what happens


"Sal" wrote:

Sorry this:
=SUMPRODUCT(--(ISNUMBER(MATCH(A4:A31,{"Advertising Concepts","Union
News"},0))),--(ISNUMBER(MATCH(C4:C31,{"Other","Pens"},0))),I4:I3 1)

"Bob Phillips" wrote:

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A1000,{"Swingline","WalMart"},0 ))),--(ISNUM
BER(MATCH(B1:B1000,{"Staples","Pens"},0))),C1:C100 0)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Sal" wrote in message
...
Hi and thank you for such a quick response.
What if I need 2 products in the column B, and sometimes 2 products from 2
different companys in Column A?
S

"Duke Carey" wrote:

If col A has the company names, col B has the products, and col C has
the
sales volumes, then

=SUMPRODUCT(--(A1:A1000="Swingline"),--(B1:B1000= "Staples"),C1:C1000)

"Sal" wrote:

Can anybody please tell me how I extract data that matches the
following:
Need to extract the sum of sales that are in a column that matches
certain
criteria.
I have a column with products that are sold by certain companys. Some
sell
pens, inks, staples etc. Different companys sell the same products.
I need all the pens and all the staples that are sold by a certain
company.
Or maybe two companys that sell pens and paper.
Perhaps ifsum(d2:d150 need pens and paper (maybe from 2 different
companys)
if match add the total values from them.
I hope I have made myself clear!!!
Thank you all.
Regards.
S

SumIf
 

You can send it to my home address - dukecarey.....at.....hotmail.com


"Sal" wrote:

Hi
Nope tried that to no avail - going bald here!!!
Sal

"Duke Carey" wrote:

Sal -

It wouldn't make it past the firewall here.

Another problem might be that the numbers you are summing are actually seen
by Excel as text. Try copying a blank/empty cell, then selecting all the
numbers & using EditPaste SpecialAdd. That will force the numbers from
text to numeric values.



"Sal" wrote:

Hi Duke
Still having problems - can I send you the worksheet details please.
If I can, please tell me how I attach it for you.
Much appreciated. Sal

"Duke Carey" wrote:

Sal -

So long as the text values in the cells match your terms exactly, this works
just fine for me. If I add a space at the end or the beginning of each, the
formula returns zero.

Make sure that all of your text values have no leading or trailing spaces &
see what happens


"Sal" wrote:

Sorry this:
=SUMPRODUCT(--(ISNUMBER(MATCH(A4:A31,{"Advertising Concepts","Union
News"},0))),--(ISNUMBER(MATCH(C4:C31,{"Other","Pens"},0))),I4:I3 1)

"Bob Phillips" wrote:

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A1000,{"Swingline","WalMart"},0 ))),--(ISNUM
BER(MATCH(B1:B1000,{"Staples","Pens"},0))),C1:C100 0)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Sal" wrote in message
...
Hi and thank you for such a quick response.
What if I need 2 products in the column B, and sometimes 2 products from 2
different companys in Column A?
S

"Duke Carey" wrote:

If col A has the company names, col B has the products, and col C has
the
sales volumes, then

=SUMPRODUCT(--(A1:A1000="Swingline"),--(B1:B1000= "Staples"),C1:C1000)

"Sal" wrote:

Can anybody please tell me how I extract data that matches the
following:
Need to extract the sum of sales that are in a column that matches
certain
criteria.
I have a column with products that are sold by certain companys. Some
sell
pens, inks, staples etc. Different companys sell the same products.
I need all the pens and all the staples that are sold by a certain
company.
Or maybe two companys that sell pens and paper.
Perhaps ifsum(d2:d150 need pens and paper (maybe from 2 different
companys)
if match add the total values from them.
I hope I have made myself clear!!!
Thank you all.
Regards.
S