Excel - Countif
I'm trying to use this formular to pull the count of items from another sheet
under 2 conditions: belongs to a department & being serviced. The sequence of "F2+Ctrl+Shift+Enter" would not get it to work, but it seems to work on the local sheet that carries the data, curiously, minus 1 count! =COUNT(IF(('Q3'!D12:D194,"=calibrated")*('Q3'!G12: G194,"=70223"),'Q3'!H12:H194)) Here's part of the sheet that I'm confused. Please help. Thanks. M702A calibrated 70222B $100.00 T-berd 310 calibrated 70222D $300.00 MP1570A OFR 8/6/2009 70222E $0.00 156 calibrated 70222E $364.59 T-Berd 310 calibrated 70223 $300.00 T-Berd 310 calibrated 70223 $300.00 T-Berd 310 ACTIVE 8/6/2009 70223 $0.00 T-Berd 310 calibrated 70223 $300.00 T-Berd 310 calibrated 70223 $300.00 |
Excel - Countif
I think you need to do this:
=SUMPRODUCT(('Q3'!D12:D194="calibrated")*('Q3'!G12 :G194="70223")) to get a count of the number that satisfy both conditions. No need for Ctrl-Shift-Enter. Hope this helps. Pete On Aug 28, 8:32*pm, Tmt wrote: I'm trying to use this formular to pull the count of items from another sheet under 2 conditions: belongs to a department & being serviced. The sequence of "F2+Ctrl+Shift+Enter" would not get it to work, but it seems to work on the local sheet that carries the data, curiously, minus 1 count! =COUNT(IF(('Q3'!D12:D194,"=calibrated")*('Q3'!G12: G194,"=70223"),'Q3'!H12:H*194)) Here's part of the sheet that I'm confused. Please help. Thanks. M702A * * * * * * * * * calibrated * * * * * * *70222B *$100.00 T-berd 310 * * * * * * * *calibrated * * * * * *70222D *$300.00 MP1570A * * * * * * * * OFR * * 8/6/2009 * * * *70222E *$0.00 156 * * * * * * * * * * calibrated * * * * * * *70222E *$364.59 T-Berd 310 * * *calibrated * * * * * * *70223 * $300.00 T-Berd 310 * * *calibrated * * * * * * *70223 * $300.00 T-Berd 310 * * *ACTIVE *8/6/2009 * * * *70223 * $0.00 T-Berd 310 * * *calibrated * * * * * * *70223 * $300.00 T-Berd 310 * * *calibrated * * * * * * *70223 * $300.00 |
Excel - Countif
Thanks for your help. It solves the problem.
Now that if I tried to pull the 70222 with an * in the formular (to get all the /A/B/D/E in the datasheet), then the result is 0? Any solution for that? tom "Pete_UK" wrote: I think you need to do this: =SUMPRODUCT(('Q3'!D12:D194="calibrated")*('Q3'!G12 :G194="70223")) to get a count of the number that satisfy both conditions. No need for Ctrl-Shift-Enter. Hope this helps. Pete On Aug 28, 8:32 pm, Tmt wrote: I'm trying to use this formular to pull the count of items from another sheet under 2 conditions: belongs to a department & being serviced. The sequence of "F2+Ctrl+Shift+Enter" would not get it to work, but it seems to work on the local sheet that carries the data, curiously, minus 1 count! =COUNT(IF(('Q3'!D12:D194,"=calibrated")*('Q3'!G12: G194,"=70223"),'Q3'!H12:HÂ*194)) Here's part of the sheet that I'm confused. Please help. Thanks. M702A calibrated 70222B $100.00 T-berd 310 calibrated 70222D $300.00 MP1570A OFR 8/6/2009 70222E $0.00 156 calibrated 70222E $364.59 T-Berd 310 calibrated 70223 $300.00 T-Berd 310 calibrated 70223 $300.00 T-Berd 310 ACTIVE 8/6/2009 70223 $0.00 T-Berd 310 calibrated 70223 $300.00 T-Berd 310 calibrated 70223 $300.00 |
Excel - Countif
Yes, try it this way:
=SUMPRODUCT(('Q3'!D12:D194="calibrated")*(LEFT('Q3 '!G12:G194,5) ="70222")) You can't use an asterisk as a wilcard in this situation. Hope this helps. Pete On Aug 28, 10:03*pm, Tmt wrote: Thanks for your help. It solves the problem. Now that if I tried to pull the 70222 with an * in the formular (to get all the /A/B/D/E in the datasheet), then the result is 0? Any solution for that? tom "Pete_UK" wrote: I think you need to do this: =SUMPRODUCT(('Q3'!D12:D194="calibrated")*('Q3'!G12 :G194="70223")) to get a count of the number that satisfy both conditions. No need for Ctrl-Shift-Enter. Hope this helps. Pete On Aug 28, 8:32 pm, Tmt wrote: I'm trying to use this formular to pull the count of items from another sheet under 2 conditions: belongs to a department & being serviced. The sequence of "F2+Ctrl+Shift+Enter" would not get it to work, but it seems to work on the local sheet that carries the data, curiously, minus 1 count! =COUNT(IF(('Q3'!D12:D194,"=calibrated")*('Q3'!G12: G194,"=70223"),'Q3'!H12:H**194)) Here's part of the sheet that I'm confused. Please help. Thanks. M702A * * * * * * * * * calibrated * * * * * * *70222B *$100.00 T-berd 310 * * * * * * * *calibrated * * * * * *70222D *$300.00 MP1570A * * * * * * * * OFR * * 8/6/2009 * * * *70222E *$0.00 156 * * * * * * * * * * calibrated * * * * * * *70222E *$364.59 T-Berd 310 * * *calibrated * * * * * * *70223 * $300.00 T-Berd 310 * * *calibrated * * * * * * *70223 * $300.00 T-Berd 310 * * *ACTIVE *8/6/2009 * * * *70223 * $0.00 T-Berd 310 * * *calibrated * * * * * * *70223 * $300.00 T-Berd 310 * * *calibrated * * * * * * *70223 * $300.00- Hide quoted text - - Show quoted text - |
All times are GMT +1. The time now is 08:37 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com