Hello, I am trying to apply a vlookup / countif combination on a simple dataset.
I have several consecutive zones of varying lengths. Some actions have been applied on subsections within these zones therefore I want to count how much has been completed.
For example;
Zone 1: 50 to 65, Action 1 was completed between 60 and 70, therefore the count is 5.
Please see attached worksheet with manually completed desired output.
As always your help is much appreciated.
Thanks.
Your numbers don't seem to make any sense to me. Maybe I'm missing something?
I, also, am not sure what you are trying to do here
Keep in mind that while you know exactly what you are doing, we have no clue whatsoever
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Hmmm, okay;
Say I have zones of various distance; 1-10km, 11-20km, 21-30km. This is defined in the first table.
Then I have a second table which comprises actions within the defined zones i.e. between 3-6km (count as 4km within zone 1), 18-25km (count as 3km in zone 2 and 5km in zone 3).
I need a lookup / countif formula within column E of my example sheet that will lookup the range and then count values for each zone for actions completed within my second table.
Its simple, just difficult to describe. Let me know if this further explanation helps.
Cheers
Maybe explain how you arrive at 13 for the first highlighted value.
Okay, so Section 1 ranges from 50km to 60km.
Out of the 5 actions, Action 1 and Action 2 cover part of this zone. Action 1 running from 52km to 60km (8km) and Action 2 running from 60km to 65km (5km) So in total 13km of Section 1 is complete from Actions 1 and 2.
Thanks
In E3 then drag down
=MAX(0,MIN($I$3,$C3)-MAX($H$3,$B3))+MAX(0,MIN($I$4,$C3)-MAX($H$4,$B3))+MAX(0,MIN($I$5,$C3)-MAX($H$5,$B3))+MAX(0,MIN($I$6,$C3)-MAX($H$6,$B3))+MAX(0,MIN($I$7,$C3)-MAX($H$7,$B3))
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
A little differently. Array entered returns same results.
If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.Formula:
=SUM(IF(MMULT((ROW(INDIRECT(B3*2&":"&C3*2))>=TRANSPOSE($H$3:$H$7*2))*(ROW(INDIRECT(B3*2&":"&C3*2)) <
TRANSPOSE($I$3:$I$7*2)),{1;1;1;1;1})/2,0.5))
Dave
Here is another formula which will have fixed length irrespective of number of "Actions".
ARRAY formula in E3 then drag down.
=SUM(IF(($B3<$I$3:$I$7)*($C3>$H$3:$H$7),IF($C3>=$I$3:$I$7,IF($B3<=$H$3:$H$7,$I$3:$I$7-$H$3:$H$7,$I$3:$I$7-$B3),IF($B3<=$H$3:$H$7,$C3-$H$3:$H$7,$C3-$B3))))
Managed to shorten the formula in my previous post #8. Still array entered.
Formula:=SUM(MMULT((ROW(INDIRECT(B3*2&":"&C3*2))/2>=TRANSPOSE($H$3:$H$7))*(ROW(INDIRECT(B3*2&":"&C3*2))/2 <
TRANSPOSE($I$3:$I$7)),{1;1;1;1;1}/2))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks