| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIFS error
Price Type AGENT
$84,000.00 L JONES All I want to do is get a sum of the price column if type=L and agent =JONES Here is what I have but I keep getting a Value error?? =SUMIFS(B46:B77,C46:C77,"L",G46:H77,"JONES") I don't see what it is wrong. I read one of the posts stating to put a value in each cell to correct the bug but that did not work either. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMIFS error
SUMIF is designed to look at one range, compare it to a criteria, then sum
the range or another range.It is not able to handle two criteria; but SUMPRODUCT is. =SUMPRODUCT(--(C46:C77="L"),--(G46:H77="JONES"), B46:B77) For details see: http://www.xldynamic.com/source/xld.SUMPRODUCT.html http://mcgimpsey.com/excel/formulae/doubleneg.html best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "NicoleC" wrote in message ... Price Type AGENT $84,000.00 L JONES All I want to do is get a sum of the price column if type=L and agent =JONES Here is what I have but I keep getting a Value error?? =SUMIFS(B46:B77,C46:C77,"L",G46:H77,"JONES") I don't see what it is wrong. I read one of the posts stating to put a value in each cell to correct the bug but that did not work either. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMIFS error
If you were here I'd kiss you!!
Thank you, thank you! "Bernard Liengme" wrote: SUMIF is designed to look at one range, compare it to a criteria, then sum the range or another range.It is not able to handle two criteria; but SUMPRODUCT is. =SUMPRODUCT(--(C46:C77="L"),--(G46:H77="JONES"), B46:B77) For details see: http://www.xldynamic.com/source/xld.SUMPRODUCT.html http://mcgimpsey.com/excel/formulae/doubleneg.html best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "NicoleC" wrote in message ... Price Type AGENT $84,000.00 L JONES All I want to do is get a sum of the price column if type=L and agent =JONES Here is what I have but I keep getting a Value error?? =SUMIFS(B46:B77,C46:C77,"L",G46:H77,"JONES") I don't see what it is wrong. I read one of the posts stating to put a value in each cell to correct the bug but that did not work either. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMIFS error
It I were there I'd have a red face. Thanks!
-- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "NicoleC" wrote in message ... If you were here I'd kiss you!! Thank you, thank you! "Bernard Liengme" wrote: SUMIF is designed to look at one range, compare it to a criteria, then sum the range or another range.It is not able to handle two criteria; but SUMPRODUCT is. =SUMPRODUCT(--(C46:C77="L"),--(G46:H77="JONES"), B46:B77) For details see: http://www.xldynamic.com/source/xld.SUMPRODUCT.html http://mcgimpsey.com/excel/formulae/doubleneg.html best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "NicoleC" wrote in message ... Price Type AGENT $84,000.00 L JONES All I want to do is get a sum of the price column if type=L and agent =JONES Here is what I have but I keep getting a Value error?? =SUMIFS(B46:B77,C46:C77,"L",G46:H77,"JONES") I don't see what it is wrong. I read one of the posts stating to put a value in each cell to correct the bug but that did not work either. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMIFS error
But sumifs is different, it's a new function in Excel 2007 and the OP is
using it correctly =SUMIFS(Sum_Range,Criteria_Range1,Criteria1,Criter ia_Range2,Criteria2) I have an example using it over multiple sheet here http://www.nwexcelsolutions.com/Excel_2007_page.htm I remember reporting a bug back in 2006 if there were empty cells in the criteria ranges you would get a value error and they said it would be fixed. I don't have 2007 where I am at the moment but maybe the OP has a beta version? -- Regards, Peo Sjoblom "Bernard Liengme" wrote in message ... SUMIF is designed to look at one range, compare it to a criteria, then sum the range or another range.It is not able to handle two criteria; but SUMPRODUCT is. =SUMPRODUCT(--(C46:C77="L"),--(G46:H77="JONES"), B46:B77) For details see: http://www.xldynamic.com/source/xld.SUMPRODUCT.html http://mcgimpsey.com/excel/formulae/doubleneg.html best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "NicoleC" wrote in message ... Price Type AGENT $84,000.00 L JONES All I want to do is get a sum of the price column if type=L and agent =JONES Here is what I have but I keep getting a Value error?? =SUMIFS(B46:B77,C46:C77,"L",G46:H77,"JONES") I don't see what it is wrong. I read one of the posts stating to put a value in each cell to correct the bug but that did not work either. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMIFS error
I don't have the beta version. I have the full version.
"Peo Sjoblom" wrote: But sumifs is different, it's a new function in Excel 2007 and the OP is using it correctly =SUMIFS(Sum_Range,Criteria_Range1,Criteria1,Criter ia_Range2,Criteria2) I have an example using it over multiple sheet here http://www.nwexcelsolutions.com/Excel_2007_page.htm I remember reporting a bug back in 2006 if there were empty cells in the criteria ranges you would get a value error and they said it would be fixed. I don't have 2007 where I am at the moment but maybe the OP has a beta version? -- Regards, Peo Sjoblom "Bernard Liengme" wrote in message ... SUMIF is designed to look at one range, compare it to a criteria, then sum the range or another range.It is not able to handle two criteria; but SUMPRODUCT is. =SUMPRODUCT(--(C46:C77="L"),--(G46:H77="JONES"), B46:B77) For details see: http://www.xldynamic.com/source/xld.SUMPRODUCT.html http://mcgimpsey.com/excel/formulae/doubleneg.html best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "NicoleC" wrote in message ... Price Type AGENT $84,000.00 L JONES All I want to do is get a sum of the price column if type=L and agent =JONES Here is what I have but I keep getting a Value error?? =SUMIFS(B46:B77,C46:C77,"L",G46:H77,"JONES") I don't see what it is wrong. I read one of the posts stating to put a value in each cell to correct the bug but that did not work either. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMIFS error
Time for new glasses! I read SUMIF without the S and I nearly told OP about
the new feature in XL2007 -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Peo Sjoblom" wrote in message ... But sumifs is different, it's a new function in Excel 2007 and the OP is using it correctly =SUMIFS(Sum_Range,Criteria_Range1,Criteria1,Criter ia_Range2,Criteria2) I have an example using it over multiple sheet here http://www.nwexcelsolutions.com/Excel_2007_page.htm I remember reporting a bug back in 2006 if there were empty cells in the criteria ranges you would get a value error and they said it would be fixed. I don't have 2007 where I am at the moment but maybe the OP has a beta version? -- Regards, Peo Sjoblom "Bernard Liengme" wrote in message ... SUMIF is designed to look at one range, compare it to a criteria, then sum the range or another range.It is not able to handle two criteria; but SUMPRODUCT is. =SUMPRODUCT(--(C46:C77="L"),--(G46:H77="JONES"), B46:B77) For details see: http://www.xldynamic.com/source/xld.SUMPRODUCT.html http://mcgimpsey.com/excel/formulae/doubleneg.html best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "NicoleC" wrote in message ... Price Type AGENT $84,000.00 L JONES All I want to do is get a sum of the price column if type=L and agent =JONES Here is what I have but I keep getting a Value error?? =SUMIFS(B46:B77,C46:C77,"L",G46:H77,"JONES") I don't see what it is wrong. I read one of the posts stating to put a value in each cell to correct the bug but that did not work either. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMIFS error
The beta was a full version, I just checked on my Excel 2007 at home and I
had no problems using your formula with blanks in the ranges. What does it say if you click the office button, then excel options, then resources and about Microsoft office excel 2007? -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com (Remove ^^ from email) "NicoleC" wrote in message ... I don't have the beta version. I have the full version. "Peo Sjoblom" wrote: But sumifs is different, it's a new function in Excel 2007 and the OP is using it correctly =SUMIFS(Sum_Range,Criteria_Range1,Criteria1,Criter ia_Range2,Criteria2) I have an example using it over multiple sheet here http://www.nwexcelsolutions.com/Excel_2007_page.htm I remember reporting a bug back in 2006 if there were empty cells in the criteria ranges you would get a value error and they said it would be fixed. I don't have 2007 where I am at the moment but maybe the OP has a beta version? -- Regards, Peo Sjoblom "Bernard Liengme" wrote in message ... SUMIF is designed to look at one range, compare it to a criteria, then sum the range or another range.It is not able to handle two criteria; but SUMPRODUCT is. =SUMPRODUCT(--(C46:C77="L"),--(G46:H77="JONES"), B46:B77) For details see: http://www.xldynamic.com/source/xld.SUMPRODUCT.html http://mcgimpsey.com/excel/formulae/doubleneg.html best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "NicoleC" wrote in message ... Price Type AGENT $84,000.00 L JONES All I want to do is get a sum of the price column if type=L and agent =JONES Here is what I have but I keep getting a Value error?? =SUMIFS(B46:B77,C46:C77,"L",G46:H77,"JONES") I don't see what it is wrong. I read one of the posts stating to put a value in each cell to correct the bug but that did not work either. |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMIFS error
Microsoft Office Excel 2007 (12.0.4518.1014)
"Peo Sjoblom" wrote: The beta was a full version, I just checked on my Excel 2007 at home and I had no problems using your formula with blanks in the ranges. What does it say if you click the office button, then excel options, then resources and about Microsoft office excel 2007? -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com (Remove ^^ from email) "NicoleC" wrote in message ... I don't have the beta version. I have the full version. "Peo Sjoblom" wrote: But sumifs is different, it's a new function in Excel 2007 and the OP is using it correctly =SUMIFS(Sum_Range,Criteria_Range1,Criteria1,Criter ia_Range2,Criteria2) I have an example using it over multiple sheet here http://www.nwexcelsolutions.com/Excel_2007_page.htm I remember reporting a bug back in 2006 if there were empty cells in the criteria ranges you would get a value error and they said it would be fixed. I don't have 2007 where I am at the moment but maybe the OP has a beta version? -- Regards, Peo Sjoblom "Bernard Liengme" wrote in message ... SUMIF is designed to look at one range, compare it to a criteria, then sum the range or another range.It is not able to handle two criteria; but SUMPRODUCT is. =SUMPRODUCT(--(C46:C77="L"),--(G46:H77="JONES"), B46:B77) For details see: http://www.xldynamic.com/source/xld.SUMPRODUCT.html http://mcgimpsey.com/excel/formulae/doubleneg.html best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "NicoleC" wrote in message ... Price Type AGENT $84,000.00 L JONES All I want to do is get a sum of the price column if type=L and agent =JONES Here is what I have but I keep getting a Value error?? =SUMIFS(B46:B77,C46:C77,"L",G46:H77,"JONES") I don't see what it is wrong. I read one of the posts stating to put a value in each cell to correct the bug but that did not work either. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SUMIFS() error? | Excel Worksheet Functions | |||
| Excel CountIfs() and SumIfs() question | Excel Worksheet Functions | |||
| Excel 2007 SUMIFS | Excel Worksheet Functions | |||
| SUMIFS with dates | Excel Worksheet Functions | |||
| SumIfs | Excel Discussion (Misc queries) | |||
Linear Mode
