 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / May 2008CountIf with VBA
Hi all, I am a newbie in excel and having a problem with countif function. Currently in the actual worksheet I was unable to set a CountIf formula with multiple criteria. Can we set it with excel VBA? Or just like using Dlookup function to find excatly match row contents. Please advise. Rgds, Shiro More info with data, layout and before and after examples of what you want.  SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > Hi all, > I am a newbie in excel and having a problem with [quoted text clipped - 8 lines] > > Shiro Msgbox = Activesheet.Evaluate("SUMPRODUCT(--(A2:A20=""some value""),--(B2:B20=25))") as an example SignatureHTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Hi all, > I am a newbie in excel and having a problem with [quoted text clipped - 8 lines] > > Shiro You can only use COUNTIF with a single criteria. If you have multiple criteria then you would normally use SUMPRODUCT, along the lines of: =SUMPRODUCT((criteria_1)*(criteria_2)*(criteria_3)* etc ) where the criteria may be of the form: (A1:A100="X") or (B1:B100>=0) Try it with your data, or post back with further details if you need more assistance. Hope this helps. Pete > Hi all, > I am a newbie in excel and having a problem with [quoted text clipped - 8 lines] > > Shiro >Hi all, >I am a newbie in excel and having a problem with [quoted text clipped - 8 lines] > >Shiro What version of Excel are you using? --ron Hi,Thank's for the response I work with excel 2000.Let see my sample data lay out Column : A B D E F G DATA : OK C3S 81A.01 818 354 formula must return 2 NG C3S 81A.01 818 351 formula return 1 NG C3S 81A.01 818 354 formula return 1 OK C3S 81A.01 818 354 formula must return 2 From that data we can see there are two excatly same data on row 1 and row 4.And I want to be able to count the number of excatly similar data on cell G. The data type are : A: Text B: Text D: Text E: Number F: Number I have tried SUMPRODUCT but it dependanciesonly to the value at column F. Pivot table work fine but this time I need to try another way first before using pivot table. Rgds, Shiro > >Hi all, > >I am a newbie in excel and having a problem with [quoted text clipped - 11 lines] > What version of Excel are you using? > --ron Make an extra column where you combine all your columns in one using the &. G1=A1&B1&D1&E1&F1 In H1 you get the result when you use the COUNTIF function. H1=COUNTIF($G$1:$G$4;G1) Result=2 H2=COUNTIF($G$1:$G$4;G2) Result=1 H3=... Rene > Hi,Thank's for the response > I work with excel 2000.Let see my sample data lay out [quoted text clipped - 43 lines] >> What version of Excel are you using? >> --ron I'm sorry Rene,does it work for you?It doesn't for me. It rationally to join all criteria in one cell but it always return 0 (zero) for me. Thank's for the response. Rgds, Shiro > Make an extra column where you combine all your columns in one using the &. > G1=A1&B1&D1&E1&F1 [quoted text clipped - 52 lines] > >> What version of Excel are you using? > >> --ron It works on my computer. G1=A1 & B1 & D1 & E1 & F1 H1=COUNTIF($G$1:$G$4;G1) Result=2 Everytime Rene > I'm sorry Rene,does it work for you?It doesn't for me. > It rationally to join all criteria in one cell but it always [quoted text clipped - 68 lines] >> >> What version of Excel are you using? >> >> --ron I can't get it. > It works on my computer. > G1=A1 & B1 & D1 & E1 & F1 [quoted text clipped - 76 lines] > >> >> What version of Excel are you using? > >> >> --ron This works so =IF(COUNTIF($M$11:M11,M11)<>1,"",COUNTA($M$11:M11,M11)) >=COUNTIF($G$1:$G$4;G1) to =COUNTIF($G$1:$G4;G1) SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > Make an extra column where you combine all your columns in one using the > &. [quoted text clipped - 56 lines] >>> What version of Excel are you using? >>> --ron >Hi,Thank's for the response > I work with excel 2000.Let see my sample data lay out [quoted text clipped - 26 lines] > >Shiro Excel 2000 does not have the COUNTIFS function which would allow you to set multiple criteria, so you need to use SUMPRODUCT. If you are trying to match "entire lines", and if your data range is, for example, rows 1:10, then one method of doing that, with sumproduct, is: =SUMPRODUCT(--($A$1:$A$10=A1),--($B$1:$B$10=B1),--($C$1:$C$10=C1),--($D$1:$D$10=D1),--($E$1:$E$10=E1)) Enter that in some cell and fill down ten rows. You should also add a test to ensure there is data in the referenced cells on the particular row. E.g.: =IF(COUNTA(A1:E1)=5,SUMPRODUCT(--($A$1:$A$10=A1), --($B$1:$B$10=B1),--($C$1:$C$10=C1),--($D$1:$D$10=D1),--($E$1:$E$10=E1)),"") =IF(COUNTA(A1:E1)=5,SUMPRODUCT(--($A$1:$A$10=A1),--($B$1:$B$10=B1), --($C$1:$C$10=C1),--($D$1:$D$10=D1),--($E$1:$E$10=E1)),"") Don't forget to "double up" on the quote marks when you set the formula in VBA: "=IF(COUNTA(A1:E1)=5,SUMPRODUCT(--($A$1:$A$10=A1),--($B$1:$B$10=B1), --($C$1:$C$10=C1),--($D$1:$D$10=D1),--($E$1:$E$10=E1)),"""")" or, if you are using the R1C1 reference style in VBA, something like: "=IF(COUNTA(RC[-8]:RC[-4])=5,SUMPRODUCT(--(R1C1:R10C1=RC[-8]),--(R1C2:R10C2=RC[-7]), --(R1C3:R10C3=RC[-6]),--(R1C4:R10C4=RC[-5]),--(R1C5:R10C5=RC[-4])),"""")" --ron Mr Ron, this formula =SUMPRODUCT(--(G:N=G16),--(G:N=K16),--(G:N=L16),--(G:N=M16),--(G:N=N16)) return #NUM! for me... > >Hi,Thank's for the response > > I work with excel 2000.Let see my sample data lay out [quoted text clipped - 32 lines] > If you are trying to match "entire lines", and if your data range is, for > example, rows 1:10, then one method of doing that, with sumproduct, is: =SUMPRODUCT(--($A$1:$A$10=A1),--($B$1:$B$10=B1),--($C$1:$C$10=C1),--($D$1:$D $10=D1),--($E$1:$E$10=E1)) > Enter that in some cell and fill down ten rows. > [quoted text clipped - 13 lines] > > or, if you are using the R1C1 reference style in VBA, something like: "=IF(COUNTA(RC[-8]:RC[-4])=5,SUMPRODUCT(--(R1C1:R10C1=RC[-8]),--(R1C2:R10C2= RC[-7]), > --(R1C3:R10C3=RC[-6]),--(R1C4:R10C4=RC[-5]),--(R1C5:R10C5=RC[-4])),"""")" > --ron You can't use full-column references in SUMPRODUCT in Excel 2000 - specify the exact ranges (or make them a bit bigger). Pete > Mr Ron, > this formula > =SUMPRODUCT(--(G:N=G16),--(G:N=K16),--(G:N=L16),--(G:N=M16),--(G:N=N16)) > return #NUM! for me... >Mr Ron, >this formula >=SUMPRODUCT(--(G:N=G16),--(G:N=K16),--(G:N=L16),--(G:N=M16),--(G:N=N16)) >return #NUM! for me... Yes it will. I did not use full-column references in my example, and you cannot in Excel 2000. Change your references to something like: G1:N65535 (or smaller) and it should work. The smaller your range, the faster will be the calculation, so I wouldn't unnecessarily make the reference as large as you have. --ron Ups !! Mr Ron, My mistake.It works like you say. Thank's. But it mean we have to edit the formula if we add new data.Did you think about a way to not editing the formula if we add new data? > >Mr Ron, > >this formula [quoted text clipped - 13 lines] > unnecessarily make the reference as large as you have. > --ron >Ups !! >Mr Ron, [quoted text clipped - 3 lines] >new data.Did you think about a way to not editing >the formula if we add new data? Since this formula will handle up to 65,535 rows of data, I did not consider that you might be adding more. If so, you could change to Excel 2007, which allows over 1,000,000 rows of data. --ron
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.