Now I am on to my next step in which is figuring out a formula so that when I add to my tabular data it will pick up the skills and regions listed.
For example, if I have four (4) VMAX skills in South Region and only one (1) VMAX skill in Federal Region and three (3) Avamar skills in West Region and zero (0) Avamar skills in Global Alliance Region and seven (7) Isilon skills in West Region and two (2) in Federal Region.
The formula(s) I have attempted to use but it is not working (mind you I am not anywhere near an advanced level in using excel):
I did not add in all variables as I was testing to see if it worked:
=SUM(COUNTIFS(A3:A28,{"*VMAX*","*Avamar*","*Isilon*"},B3:B28,{"*South*";"*Northeast*";"*Federal*";"*Canada*";"*Central*"}))
Again I am not adding in all my variables since I am unsure of how to equate the numbers from Row A and Row B to coincide with each Region per each skill into Row C for a total so that I can eventually do a formula for percentage based on the number of job postings.
=COUNTIFS(A4:A29,{"*VMAX*","*Avamar*","*Isilon*"},B4:B29,{"*South*";"*Northeast*";"*Federal*";"*Canada*";"*Central*"})
Job_Posting_and_Skill_Analysis template
| Skill | Region | # | % | Job Posting ID | |
| ServiceNow implementation | SDS - Federal | 1 | EGPSJP00003473 | ||
| VMAX | Global Alliance | 1 | EGPSJP00003469 | ||
| Virtual Control Software | SDS - Northeast | 11 | EGPSJP00003474 | ||
| Isilon | SDS - Northeast | 11 | EGPSJP00003476 | ||
| SAN | SDS - Northeast | 10 | EGPSJP00003477 | ||
| Program Manager | SDS - Northeast | 10 | EGPSJP00003452 | ||
| VMAX | SDS - South | 10 | EGPSJP00003458 | ||
| VNX | SDS - South | 9 | EGPSJP00003459 | ||
| VMAX | SDS - Northeast | 9 | EGPSJP00003449 | ||
| VxRack | SDS - Northeast | 8 | EGPSJP00003450 | ||
| VXrail | SDS - Northeast | 8 | EGPSJP00003453 | ||
| VPLEX | SDS - South | 8 | EGPSJP00003478 | ||
| GS: Avamar - Data Domain Solution Administer | SDS - Federal | 8 | EGPSJP00003454 | ||
| GS: Avamar Administer | SDS - Federal | 7 | EGPSJP00003465 | ||
| GS: Avamar Administer | SDS - South | 6 | EGPSJP00003464 | ||
| GS: Avamar Administer | Federal | 5 | EGPSJP00003475 | ||
| GS: Centera Design | SDS - West | 4 | EGPSJP00003479 | ||
| GS: Data Domain Administer | SDS - South | 4 | EGPSJP00003448 | ||
| GS: Symmetrix Migration Design | Global Alliance | 4 | EGPSJP00003463 |
I need to keep this in a tabular data format so that I can eventually get to creating my end result of a interactive dashboard. Any guidance would be appreciated, links or examples. I am currently looking up the formulas now in the Excel Formulas, Lookup and Return Multiple Matches. Thank you, Adriane
Hello,
Checked your attached file a little closer now, so removing my previous post.
I am not sure what it is you want to get here, can you elaborate and perhaps describe what result you expect?
/Anders
