Complicated lookup result

M

matzy

New Member
Joined
Apr 14, 2014
Messages
12
Good evening all, I've used this forum many times as a guest and discovered numerous results for problematic formula I am working with, however, this one has finally got me completely stuck so i'm hoping an excel guru out there may be able to help me. I am assuming I would need to use VBA to achieve this and although I am fairly experienced in using VB.Net to write standalone applications I have never used it in Excel before so not too sure how alike they are.

I have added an image of a small part of my spreadsheet and will attempt to explain what I am trying to achieve.

Firstly, I have a column of roles C8:C20. The following columns show availability of staff, a blank cell shows them as available, a cell with a code means they are not available for whatever reason. There are a total of 13 staff, in column D at 00:00hrs no one is booked off therefore the total available is 13 as circled. In column J we have four staff booked off leaving a total of 9 staff available as circled in the entire column.

What I am trying to achieve is to find out what roles are available in that hour. For example. Under row J I would want the following totals if possible:

OIC = 2 (We have 3 when fully staffed but 1 is booked off)
DVR = 2 (None are booked off)
FF = 5 (We have 7 when fully staffed but 2 are booked off)
NQ = 0 (We have 1 when fully staffed but that one is booked off)

2 + 2 + 5 gives me the total of 9 in the heading of column J but I wanted to break that down to show me what the individual totals are below each column.. Is this at all possible?

excel_example.png


Any help you can give would be greatly appreciated.
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Sort by date Sort by votes
Welcome to the board.

If you have XL2007+ you can use the COUNTIFS function..

=COUNTIFS(C8:C20,"OIC",J8:J20,"")
 
Upvote 0
Wow, Jonmo, what can I say.. Thank you so much, it works perfectly.

I certainly did not expect a reply so quick or for it to be such a straightforward formula. Yet again this forum delivers and I cann thanks you enough.
 
Upvote 0
One slight amendment I would like to make if possible is that someone could have a multiple role, for example C9 could be DVR FF and this would increase the total for both OIC and FF?
 
Upvote 0
Try this:

Code: 
=SUMPRODUCT(-($J$8:$J$20=""),-ISNUMBER(SEARCH(" OIC "," "&$C$8:$C$20&" ")))

Markmzz
 
Upvote 0
matzy said:
Thanks Markmzz, I just tried that and I just get a result of 0 (zero) !!
Click to expand...

Here the formula worked. Look at this:

Layout

CodeResult
OIC2
DVR3
FF6
NQ0
Role
*
OIC w
DVR
OIC
OIC
DVR FF
FF
FF DVR
FF
FF
FF
FF w
FF w
NQ w
*********************************
<colgroup><col width="17" style="width: 13pt; mso-width-source: userset; mso-width-alt: 621;" span="2"> <col width="42" style="width: 32pt; mso-width-source: userset; mso-width-alt: 1536;"> <col width="32" style="width: 24pt; mso-width-source: userset; mso-width-alt: 1170;"> <col width="37" style="width: 28pt; mso-width-source: userset; mso-width-alt: 1353;"> <col width="17" style="width: 13pt; mso-width-source: userset; mso-width-alt: 621;" span="4"> <col width="27" style="width: 20pt; mso-width-source: userset; mso-width-alt: 987;"> <tbody> </tbody>

Formula

Code: 
In E2

=SUMPRODUCT(-($J$8:$J$20=""),-ISNUMBER(SEARCH(" "&$D2&" "," "&$C$8:$C$20&" ")))

And copy down.

Markmzz
 
Upvote 0
Perfect.. I just tried it again and it does exactly what I want.. thank you very much Markmzz
 
Upvote 0
No problem at all Mark, I really appreciate your help. I do have one more thing that i'm struggling with and that is to work out whether I have the correct combination of staff available. If all staff had a set role it would be easy, but having the multiple roles is whats making it hard for me, especially as a person cannot utilise more than one role at a time..

for example. A minimum requirement of roles is 1 x OIC, 1 x DVR, 2 x FF and one person cannot operate as a multiple role, therefore I need 4 individuals that can accomplish the 1 x OIC, 1 x DVR, 2 x FF requirement.

If it helps I could send you the actual spreadsheet i'm working on with a much better description of what i'm trying to achieve. Assuming you have the time and do not mind.
 
Upvote 0
You must log in or register to reply here.

Similar threads

tezza
  • Solved
Double Shifts Breakdown
Replies
4
Views
363
tezza
tezza
B
  • Question
populating the next available row in a shared workbook
Replies
1
Views
261
rondeondo
rondeondo
M
  • Question
Divide any value with divide number and return either zero if fully divided or show value which is remaining but less than divided number
Replies
5
Views
288
Fluff
Fluff
D
  • Question
Count ifs formula
Replies
2
Views
344
Herakles
H
F
  • Question
Excel Macro Assistance Needed for Staff Roster and Training Allocation
Replies
0
Views
307
FajnaAli
F

Forum statistics

Threads
1,214,918
Messages
6,122,257
Members
449,075
Latest member
staticfluids

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top