| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to use SUMIF to return sums between two values located in cells | Excel Worksheet Functions | |||
| Sumif of Sumif perhaps? | Excel Discussion (Misc queries) | |||
| SUMIF | Excel Worksheet Functions | |||
| SUMIF with Mutiple Ranges & Criteria | Excel Discussion (Misc queries) | |||
| SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions | |||
Linear Mode
