SOLVED

Vlookup and Countif formula - Help

%3CLINGO-SUB%20id%3D%22lingo-sub-2684409%22%20slang%3D%22en-US%22%3EVlookup%20and%20Countif%20formula%20-%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2684409%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20worksheet%20that%20I%20use%20vLookup%20in%20to%20look%20up%20the%20company%20and%20then%20the%20product%20and%20it%20puts%20an%20X.%26nbsp%3B%20Now%20I%20need%20it%20to%20look%20up%20the%20company%2C%20as%20well%20has%20the%20product%20and%20calculate%20how%20many%20of%20that%20product.%26nbsp%3B%3C%2FP%3E%3CP%3EExample%20below%20is%20the%20Company%201%2C%202%2C%20and%203%20have%202%20products%20and%20Company%204%20has%201%20product.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20the%20formula%20I%20use%20currently%20that%20puts%20an%20X%20if%20the%20company%20has%20the%20product%2C%20I%20have%20tried%20to%20incorporate%20the%20countif%20in%20the%20formula%20however%20I%20either%20get%20errors%2C%20N%2FA%2C%20or%20X.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(ISERROR(VLOOKUP(%24A2312%26amp%3BAJ%243%2C'Billable%20Products'!%24H%243%3A%24H%2413000%2C1%2C0))%2C%22%22%2C%22X%22)%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Excelling5_0-1629908864870.png%22%20style%3D%22width%3A%20135px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F305686i75F6028F2B76CD1A%2Fimage-dimensions%2F135x122%3Fv%3Dv2%22%20width%3D%22135%22%20height%3D%22122%22%20role%3D%22button%22%20title%3D%22Excelling5_0-1629908864870.png%22%20alt%3D%22Excelling5_0-1629908864870.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Excelling5_1-1629908873770.png%22%20style%3D%22width%3A%20115px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F305687i7F846008C7B2578D%2Fimage-dimensions%2F115x384%3Fv%3Dv2%22%20width%3D%22115%22%20height%3D%22384%22%20role%3D%22button%22%20title%3D%22Excelling5_1-1629908873770.png%22%20alt%3D%22Excelling5_1-1629908873770.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2684409%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2685011%22%20slang%3D%22en-US%22%3ERe%3A%20Vlookup%20and%20Countif%20formula%20-%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2685011%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1137354%22%20target%3D%22_blank%22%3E%40Excelling5%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20formula%20you%20post%20has%20nothing%20in%20it%20about%20counting...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20the%20images%20are%20not%20any%20where%20near%20as%20helpful%20as%20the%20actual%20spreadsheet.%20Is%20it%20possible%20for%20you%20to%20post%20even%20this%20mockup%2C%20making%20sure%20that%20it%20is%20an%20accurate%20representation%20of%20your%20actual%3F%20Ideally%2C%20unless%20there's%20confidential%20info%20that%20you're%20not%20at%20liberty%20to%20share%2C%20post%20the%20real%20spreadsheet.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2685052%22%20slang%3D%22en-US%22%3ERe%3A%20Vlookup%20and%20Countif%20formula%20-%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2685052%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%20Thank%20you%20for%20the%20feedback%2C%20it%20is%20a%20confidential%20info%20and%20the%20report%20itself%20is%20massive%20with%20many%20tabs%20and%20different%20formulas.%26nbsp%3B%20I%20was%20trying%20to%20share%20as%20much%20as%20I%20could%20without%20posting%20the%20actual%20sheet.%26nbsp%3B%20%26nbsp%3BThat%20is%20correct%20the%20formula%20I%20have%20now%20works%20for%20what%20I%20needed%20it%20to%20do%2C%20however%2C%20there%20was%20a%20change%20that%20we%20now%20need%20to%20count%20how%20many%20of%20this%20one%20particular%20product%2C%20at%20this%20time%20the%20vlookup%20goes%20to%20the%20tab%2C%20pulls%20the%20information%20by%20customer%20and%20product%20and%20puts%20an%20X%20in%20saying%20they%20have%20this%20product.%26nbsp%3B%20I%20need%20this%20on%20the%20other%2050%20products%20and%20only%20on%201%20product%20do%20I%20need%20to%20count%20it.%26nbsp%3B%20I%20have%20been%20trying%20to%20use%20the%20vlookup%20with%20the%20countif%20statements%20however%2C%20I%20am%20getting%20errors.%26nbsp%3B%20I%20was%20looking%20to%20see%20if%20someone%20out%20there%20has%20used%20the%20vlookup%20and%20countif%20in%20a%20formula%20together%20and%20how%20they%20may%20have%20gotten%20it%20to%20work.%26nbsp%3B%20%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2685151%22%20slang%3D%22en-US%22%3ERe%3A%20Vlookup%20and%20Countif%20formula%20-%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2685151%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1137354%22%20target%3D%22_blank%22%3E%40Excelling5%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20truly%20is%20hard%20to%20give%20specific%20help%20without%20seeing%20the%20specific%20layout(s)%20of%20your%20workbook(s).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20used%20the%20various%20COUNT%20functions%20recently%20with%20the%20%3CSTRONG%3EFILTER%3C%2FSTRONG%3E%20function%20nested%20in%20it%20to%20select%20the%20rows%20to%20be%20counted.%20The%20latter%20is%20very%20new%20and%20requires%20the%20most%20recent%20version%20of%20Excel%2C%20but%20if%20you%20have%20it%2C%20it%20can%20be%20far%20more%20powerful%20than%20VLOOKUP.%20You%20could%20also%20try%20%3CSTRONG%3EXLOOKUP%3C%2FSTRONG%3E.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20a%20reference%20that%20explains%20FILTER%20and%20a%20couple%20of%20other%20equally%20powerful%20and%20complementary%20functions.%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3D9I9DtFOVPIg%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3D9I9DtFOVPIg%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBy%20the%20way%2C%20your%20reference%20to%20multiple%20tabs%20concerns%20me.%20There%20are%20many%20times%20when%20we%20see%20posted%20here%20sample%20files%20where%20multiple%20tabs%20are%20used%20to%20represent%2C%20say%2C%20different%20customers%20or%20vendors%2C%20and%20the%20products%20associated.%20Excel%20works%20VERY%20well--you%20might%20say%20it%20excels--when%20such%20data%2C%20albeit%20for%20different%20customers%20or%20vendors%20or%20products%2C%20are%20ALL%20arrayed%20on%20a%20single%20sheet%2C%20in%20a%20single%20table%2C%20with%20columnar%20identifiers%20used%20to%20designate%20the%20customer%20or%20vendor%20or%20product%20(whatever%20it%20was%20that%20supposedly%20warranted%20its%20own%20sheet).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20that%20too%20could%20be%20part%20of%20a%20solution%3A%20consider%20consolidating%20those%20multiple%20tabs%20into%20a%20single%20database.%20You%20might%20be%20astounded%20at%20how%20much%20simpler%20you've%20made%20your%20task%20when%20you%20let%20Excel%20do%20the%20%22heavy%20lifting%22%20rather%20than%20trying%20to%20%22help%22%20by%20separating%20those%20entities%20out.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I have a worksheet that I use vLookup in to look up the company and then the product and it puts an X.  Now I need it to look up the company, as well has the product and calculate how many of that product. 

Example below is the Company 1, 2, and 3 have 2 products and Company 4 has 1 product. 

 

This is the formula I use currently that puts an X if the company has the product, I have tried to incorporate the countif in the formula however I either get errors, N/A, or X.  

 

=IF(ISERROR(VLOOKUP($A2312&AJ$3,'Billable Products'!$H$3:$H$13000,1,0)),"","X")

Excelling5_0-1629908864870.png

 

Excelling5_1-1629908873770.png

 

4 Replies

@Excelling5 

 

The formula you post has nothing in it about counting...

 

And the images are not any where near as helpful as the actual spreadsheet. Is it possible for you to post even this mockup, making sure that it is an accurate representation of your actual? Ideally, unless there's confidential info that you're not at liberty to share, post the real spreadsheet.

@mathetes  Thank you for the feedback, it is a confidential info and the report itself is massive with many tabs and different formulas.  I was trying to share as much as I could without posting the actual sheet.   That is correct the formula I have now works for what I needed it to do, however, there was a change that we now need to count how many of this one particular product, at this time the vlookup goes to the tab, pulls the information by customer and product and puts an X in saying they have this product.  I need this on the other 50 products and only on 1 product do I need to count it.  I have been trying to use the vlookup with the countif statements however, I am getting errors.  I was looking to see if someone out there has used the vlookup and countif in a formula together and how they may have gotten it to work.   

best response confirmed by allyreckerman (Microsoft)
Solution

@Excelling5 

 

It truly is hard to give specific help without seeing the specific layout(s) of your workbook(s).

 

I've used the various COUNT functions recently with the FILTER function nested in it to select the rows to be counted. The latter is very new and requires the most recent version of Excel, but if you have it, it can be far more powerful than VLOOKUP. You could also try XLOOKUP.

 

Here's a reference that explains FILTER and a couple of other equally powerful and complementary functions. https://www.youtube.com/watch?v=9I9DtFOVPIg

 

By the way, your reference to multiple tabs concerns me. There are many times when we see posted here sample files where multiple tabs are used to represent, say, different customers or vendors, and the products associated. Excel works VERY well--you might say it excels--when such data, albeit for different customers or vendors or products, are ALL arrayed on a single sheet, in a single table, with columnar identifiers used to designate the customer or vendor or product (whatever it was that supposedly warranted its own sheet).

 

So that too could be part of a solution: consider consolidating those multiple tabs into a single database. You might be astounded at how much simpler you've made your task when you let Excel do the "heavy lifting" rather than trying to "help" by separating those entities out.

Learn everything about our brand-new Dynamic Arrays and how you can use them to build advanced spreadsheets. Arrays (CSE) have long been present in Excel, bu...
@mathetes
Thank you for the reference and assistance. I appreciate it!!! I will review this and see if I can get it to work.

Thank you!!!