|
countif question
hi all,
I'd like to make a summary sheet that will count the total # of product within a certain month so if I type in the month # in one cell the # of total products will show up. for example month # product 1 a 1 b 2 a so if I type in 1 for month, 2 will show up for products. thanks in advance. |
Hi Jennie
This will count the dates that are in Jan in B1:B10 =SUMPRODUCT((MONTH(B1:B10)=1)*1) -- Regards Ron de Bruin http://www.rondebruin.nl "Jennie" wrote in message ... hi all, I'd like to make a summary sheet that will count the total # of product within a certain month so if I type in the month # in one cell the # of total products will show up. for example month # product 1 a 1 b 2 a so if I type in 1 for month, 2 will show up for products. thanks in advance. |
Hi Jennie, assuming your months are in column A and your products are in column B in C1 put this formula =COUNTIF(A1:A100,D1) Now put the number of the month you want to count for into D1. This will only count the amount of times the month appears in column A. If you need help counting products per month or anything else come back HTH. -- greg7468 ------------------------------------------------------------------------ greg7468's Profile: http://www.excelforum.com/member.php...fo&userid=9031 View this thread: http://www.excelforum.com/showthread...hreadid=381263 |
I'm looking for a generic formula that will work for any month, not just
January. In place of month I tried to type in a range of cells (say A1:a10) and instead of "1" I tried to type in the cell where I will enter the month I'm interested in (say C1), but this gives me a #ref error. So I tried: =sumproduct((A1:A10(B1:B10)=c1)*c1) How can I make a generic formula that will work for any month? "Ron de Bruin" wrote: Hi Jennie This will count the dates that are in Jan in B1:B10 =SUMPRODUCT((MONTH(B1:B10)=1)*1) -- Regards Ron de Bruin http://www.rondebruin.nl "Jennie" wrote in message ... hi all, I'd like to make a summary sheet that will count the total # of product within a certain month so if I type in the month # in one cell the # of total products will show up. for example month # product 1 a 1 b 2 a so if I type in 1 for month, 2 will show up for products. thanks in advance. |
=sumproduct((A1:A10(B1:B10)=c1)*c1)
use this =sumproduct((A1:A10(B1:B10)=C1)*1) don't use *c1 -- Regards Ron de Bruin http://www.rondebruin.nl "Jennie" wrote in message ... I'm looking for a generic formula that will work for any month, not just January. In place of month I tried to type in a range of cells (say A1:a10) and instead of "1" I tried to type in the cell where I will enter the month I'm interested in (say C1), but this gives me a #ref error. So I tried: =sumproduct((A1:A10(B1:B10)=c1)*c1) How can I make a generic formula that will work for any month? "Ron de Bruin" wrote: Hi Jennie This will count the dates that are in Jan in B1:B10 =SUMPRODUCT((MONTH(B1:B10)=1)*1) -- Regards Ron de Bruin http://www.rondebruin.nl "Jennie" wrote in message ... hi all, I'd like to make a summary sheet that will count the total # of product within a certain month so if I type in the month # in one cell the # of total products will show up. for example month # product 1 a 1 b 2 a so if I type in 1 for month, 2 will show up for products. thanks in advance. |
yeah I need help with counting products per month, if you don't mind helping
me with that "greg7468" wrote: Hi Jennie, assuming your months are in column A and your products are in column B in C1 put this formula =COUNTIF(A1:A100,D1) Now put the number of the month you want to count for into D1. This will only count the amount of times the month appears in column A. If you need help counting products per month or anything else come back HTH. -- greg7468 ------------------------------------------------------------------------ greg7468's Profile: http://www.excelforum.com/member.php...fo&userid=9031 View this thread: http://www.excelforum.com/showthread...hreadid=381263 |
sorry to bother you again, I'm still getting a #ref error with the new
formula. is there another one I can use? "Ron de Bruin" wrote: =sumproduct((A1:A10(B1:B10)=c1)*c1) use this =sumproduct((A1:A10(B1:B10)=C1)*1) don't use *c1 -- Regards Ron de Bruin http://www.rondebruin.nl "Jennie" wrote in message ... I'm looking for a generic formula that will work for any month, not just January. In place of month I tried to type in a range of cells (say A1:a10) and instead of "1" I tried to type in the cell where I will enter the month I'm interested in (say C1), but this gives me a #ref error. So I tried: =sumproduct((A1:A10(B1:B10)=c1)*c1) How can I make a generic formula that will work for any month? "Ron de Bruin" wrote: Hi Jennie This will count the dates that are in Jan in B1:B10 =SUMPRODUCT((MONTH(B1:B10)=1)*1) -- Regards Ron de Bruin http://www.rondebruin.nl "Jennie" wrote in message ... hi all, I'd like to make a summary sheet that will count the total # of product within a certain month so if I type in the month # in one cell the # of total products will show up. for example month # product 1 a 1 b 2 a so if I type in 1 for month, 2 will show up for products. thanks in advance. |
Use the formula I posted and change it to this
=SUMPRODUCT((MONTH(B1:B10)=C1)*1) -- Regards Ron de Bruin http://www.rondebruin.nl "Jennie" wrote in message ... sorry to bother you again, I'm still getting a #ref error with the new formula. is there another one I can use? "Ron de Bruin" wrote: =sumproduct((A1:A10(B1:B10)=c1)*c1) use this =sumproduct((A1:A10(B1:B10)=C1)*1) don't use *c1 -- Regards Ron de Bruin http://www.rondebruin.nl "Jennie" wrote in message ... I'm looking for a generic formula that will work for any month, not just January. In place of month I tried to type in a range of cells (say A1:a10) and instead of "1" I tried to type in the cell where I will enter the month I'm interested in (say C1), but this gives me a #ref error. So I tried: =sumproduct((A1:A10(B1:B10)=c1)*c1) How can I make a generic formula that will work for any month? "Ron de Bruin" wrote: Hi Jennie This will count the dates that are in Jan in B1:B10 =SUMPRODUCT((MONTH(B1:B10)=1)*1) -- Regards Ron de Bruin http://www.rondebruin.nl "Jennie" wrote in message ... hi all, I'd like to make a summary sheet that will count the total # of product within a certain month so if I type in the month # in one cell the # of total products will show up. for example month # product 1 a 1 b 2 a so if I type in 1 for month, 2 will show up for products. thanks in advance. |
| All times are GMT +1. The time now is 09:27 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com