|
Re counting
Hi, wonder if you can help
In A1 I have a number in b1 I have two letter. All going downwards A1 B1 5 AA 3 AB 2 AA empty empty 2 AB I would like to be able to count in C1 the total of AA (7) and the total of AB(5) bearing in mind that some cells will be empty. Thanks for the help Regards Stephen |
Re counting
hi,
one way.... =countif(A1:A5000,"AA") I think I would use a sperate formula of the AB part. Regards FSt1 "pano" wrote: Hi, wonder if you can help In A1 I have a number in b1 I have two letter. All going downwards A1 B1 5 AA 3 AB 2 AA empty empty 2 AB I would like to be able to count in C1 the total of AA (7) and the total of AB(5) bearing in mind that some cells will be empty. Thanks for the help Regards Stephen |
Re counting
If I understand you correctly ......
In C1: =SUMPRODUCT((B1:B5="AA")+(B1:B5="AB"),A1:A5) This formula returns 12 which is the sum of 7 (for AA) and 5 (for AB). For Excel 2003, SUMPRODUCT does not work on columns i.e. A:A or B:B. If you want to see 7 in one cell and then 5 in another, use SUMIF. Epinn "pano" wrote in message ups.com... Hi, wonder if you can help In A1 I have a number in b1 I have two letter. All going downwards A1 B1 5 AA 3 AB 2 AA empty empty 2 AB I would like to be able to count in C1 the total of AA (7) and the total of AB(5) bearing in mind that some cells will be empty. Thanks for the help Regards Stephen |
Re counting
opps.
I misunderstood your post. instead of countif, use sumif. Regards FSt1 "FSt1" wrote: hi, one way.... =countif(A1:A5000,"AA") I think I would use a sperate formula of the AB part. Regards FSt1 "pano" wrote: Hi, wonder if you can help In A1 I have a number in b1 I have two letter. All going downwards A1 B1 5 AA 3 AB 2 AA empty empty 2 AB I would like to be able to count in C1 the total of AA (7) and the total of AB(5) bearing in mind that some cells will be empty. Thanks for the help Regards Stephen |
Re counting
For those who don't like SUMPRODUCT, try this
=SUMIF(B1:B5,"AA",A1:A5)+SUMIF(B1:B5,"AB",A1:A5) This may be more efficient for a large range. Epinn "Epinn" wrote in message ... If I understand you correctly ...... In C1: =SUMPRODUCT((B1:B5="AA")+(B1:B5="AB"),A1:A5) This formula returns 12 which is the sum of 7 (for AA) and 5 (for AB). For Excel 2003, SUMPRODUCT does not work on columns i.e. A:A or B:B. If you want to see 7 in one cell and then 5 in another, use SUMIF. Epinn "pano" wrote in message ups.com... Hi, wonder if you can help In A1 I have a number in b1 I have two letter. All going downwards A1 B1 5 AA 3 AB 2 AA empty empty 2 AB I would like to be able to count in C1 the total of AA (7) and the total of AB(5) bearing in mind that some cells will be empty. Thanks for the help Regards Stephen |
Re counting
Also:
=SUM(SUMIF(B1:B5,{"AA","AB"},A1:A5)) -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Epinn" wrote in message ... For those who don't like SUMPRODUCT, try this =SUMIF(B1:B5,"AA",A1:A5)+SUMIF(B1:B5,"AB",A1:A5) This may be more efficient for a large range. Epinn "Epinn" wrote in message ... If I understand you correctly ...... In C1: =SUMPRODUCT((B1:B5="AA")+(B1:B5="AB"),A1:A5) This formula returns 12 which is the sum of 7 (for AA) and 5 (for AB). For Excel 2003, SUMPRODUCT does not work on columns i.e. A:A or B:B. If you want to see 7 in one cell and then 5 in another, use SUMIF. Epinn "pano" wrote in message ups.com... Hi, wonder if you can help In A1 I have a number in b1 I have two letter. All going downwards A1 B1 5 AA 3 AB 2 AA empty empty 2 AB I would like to be able to count in C1 the total of AA (7) and the total of AB(5) bearing in mind that some cells will be empty. Thanks for the help Regards Stephen |
Re counting
Thanks Ricky for reminding me. Wonder if we can drop SUM in version 2007.
Haven't seen you around much lately. Hope all is well. Epinn "Ragdyer" wrote in message ... Also: =SUM(SUMIF(B1:B5,{"AA","AB"},A1:A5)) -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Epinn" wrote in message ... For those who don't like SUMPRODUCT, try this =SUMIF(B1:B5,"AA",A1:A5)+SUMIF(B1:B5,"AB",A1:A5) This may be more efficient for a large range. Epinn "Epinn" wrote in message ... If I understand you correctly ...... In C1: =SUMPRODUCT((B1:B5="AA")+(B1:B5="AB"),A1:A5) This formula returns 12 which is the sum of 7 (for AA) and 5 (for AB). For Excel 2003, SUMPRODUCT does not work on columns i.e. A:A or B:B. If you want to see 7 in one cell and then 5 in another, use SUMIF. Epinn "pano" wrote in message ups.com... Hi, wonder if you can help In A1 I have a number in b1 I have two letter. All going downwards A1 B1 5 AA 3 AB 2 AA empty empty 2 AB I would like to be able to count in C1 the total of AA (7) and the total of AB(5) bearing in mind that some cells will be empty. Thanks for the help Regards Stephen |
Re counting
I know SUMIFS is available for 2007 but no clue how it works.
Epinn "Epinn" wrote in message ... Thanks Ricky for reminding me. Wonder if we can drop SUM in version 2007. Haven't seen you around much lately. Hope all is well. Epinn "Ragdyer" wrote in message ... Also: =SUM(SUMIF(B1:B5,{"AA","AB"},A1:A5)) -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Epinn" wrote in message ... For those who don't like SUMPRODUCT, try this =SUMIF(B1:B5,"AA",A1:A5)+SUMIF(B1:B5,"AB",A1:A5) This may be more efficient for a large range. Epinn "Epinn" wrote in message ... If I understand you correctly ...... In C1: =SUMPRODUCT((B1:B5="AA")+(B1:B5="AB"),A1:A5) This formula returns 12 which is the sum of 7 (for AA) and 5 (for AB). For Excel 2003, SUMPRODUCT does not work on columns i.e. A:A or B:B. If you want to see 7 in one cell and then 5 in another, use SUMIF. Epinn "pano" wrote in message ups.com... Hi, wonder if you can help In A1 I have a number in b1 I have two letter. All going downwards A1 B1 5 AA 3 AB 2 AA empty empty 2 AB I would like to be able to count in C1 the total of AA (7) and the total of AB(5) bearing in mind that some cells will be empty. Thanks for the help Regards Stephen |
Re counting
Earning a living has a slightly higher priority on my time then the NGs.
Not quite retired yet, as are some of the folks around here.<g -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Epinn" wrote in message ... Thanks Ricky for reminding me. Wonder if we can drop SUM in version 2007. Haven't seen you around much lately. Hope all is well. Epinn "Ragdyer" wrote in message ... Also: =SUM(SUMIF(B1:B5,{"AA","AB"},A1:A5)) -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Epinn" wrote in message ... For those who don't like SUMPRODUCT, try this =SUMIF(B1:B5,"AA",A1:A5)+SUMIF(B1:B5,"AB",A1:A5) This may be more efficient for a large range. Epinn "Epinn" wrote in message ... If I understand you correctly ...... In C1: =SUMPRODUCT((B1:B5="AA")+(B1:B5="AB"),A1:A5) This formula returns 12 which is the sum of 7 (for AA) and 5 (for AB). For Excel 2003, SUMPRODUCT does not work on columns i.e. A:A or B:B. If you want to see 7 in one cell and then 5 in another, use SUMIF. Epinn "pano" wrote in message ups.com... Hi, wonder if you can help In A1 I have a number in b1 I have two letter. All going downwards A1 B1 5 AA 3 AB 2 AA empty empty 2 AB I would like to be able to count in C1 the total of AA (7) and the total of AB(5) bearing in mind that some cells will be empty. Thanks for the help Regards Stephen |
Re counting
Hi Epinn
Wonder if we can drop SUM in version 2007 No, it is alive and well in XL2007 and will remain so SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2 .) Note that the order has altered, and you give the range to be Summed first and not third as with SUMIF. Then you give the 1st criteria range and then the criteria. You can follow the criteria with up to 126 further ranges each with its own criteria (or should that be criterium <g) So you could have =SUMIFS(A1:A5,B1:B5,"AA",C1:C5,"AA",D1:D5,"AA") which would total A1:A5 where AA occurred in column B and C and D but for the scenario where you want either "AA" OR "AB" in a column you would still need to use =SUM(SUMIF(B1:B5,{"AA","AB"},A1:A5)) -- Regards Roger Govier "Epinn" wrote in message ... I know SUMIFS is available for 2007 but no clue how it works. Epinn "Epinn" wrote in message ... Thanks Ricky for reminding me. Wonder if we can drop SUM in version 2007. Haven't seen you around much lately. Hope all is well. Epinn "Ragdyer" wrote in message ... Also: =SUM(SUMIF(B1:B5,{"AA","AB"},A1:A5)) -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Epinn" wrote in message ... For those who don't like SUMPRODUCT, try this =SUMIF(B1:B5,"AA",A1:A5)+SUMIF(B1:B5,"AB",A1:A5) This may be more efficient for a large range. Epinn "Epinn" wrote in message ... If I understand you correctly ...... In C1: =SUMPRODUCT((B1:B5="AA")+(B1:B5="AB"),A1:A5) This formula returns 12 which is the sum of 7 (for AA) and 5 (for AB). For Excel 2003, SUMPRODUCT does not work on columns i.e. A:A or B:B. If you want to see 7 in one cell and then 5 in another, use SUMIF. Epinn "pano" wrote in message ups.com... Hi, wonder if you can help In A1 I have a number in b1 I have two letter. All going downwards A1 B1 5 AA 3 AB 2 AA empty empty 2 AB I would like to be able to count in C1 the total of AA (7) and the total of AB(5) bearing in mind that some cells will be empty. Thanks for the help Regards Stephen |
| All times are GMT +1. The time now is 06:59 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com