Countif

Hi,

I’m attempting to write a macro that calculates how many passengers got on a train. So if the passenger time is less than the train time add 1 to count.

As you can see I’ve highlighted what I mean, so for the 0551 train there were 4 passengers.

I’ve started but to be honest I’m a bit lost. I assume I’d need a loop to check that passenger time is less than train time, if it is then add 1 to count, if it’s not place the count in the totals cell and carry on the check for the next train at 0621 but ignoring those passengers already used for the 0551 train.

Any help much appreciated.

Cheers.

Hi romper,

a forumla is fine, i just presumed that what i was asking for would require a macro.

i may also have to add that this will be used on other stations where the train times will be different along with the passenger times.

there is yes, the WI is inbound the WO is outbound, only in this instance there is no outbound data. so the passenger data only applies to all that is WI.

for other stations i would have to take the VAR in to account, but hopefully i can figure that once this bit is clearer.

Cheers romper, this works for the first two, but then in cell F4 the answer returned is 11, it should be 7, so it is subtracting the previous total but not the one before that.

So when I copy down, F24 only subtracts F23 where it should be subtracting all of them.. if that makes sense.

I tried to change the formula but to no avail.

you're a star romper, cheers.

I might be back on here in a few days when i try this on a more complicated station with more data!

Thanks again, really appreciate it.

ok stuck again, and i know this is simple but no matter what i try i cant get it to work.

I want to find the total ticket types for each train, so i assumed i'd use the INDIRECT to count cell C2 to C(then whatever number is in E2). i planned on pairing this with a count if across the ticket types.

sheet attached.

hey romper,

i figured the code to add the cells which is

Please Login or Register  to view this content.

which adds up and gives me 4, but this isn't great as i need to nest this somehow within a sumif or countif to say within the selected data, count the number of ticket types that equal 1, and so on for the rest of the ticket types.

not sure if indirect is what it needs. as it gets more complicated to tally them up when you drag it down as it totals everything up from cell C2.

it's like what you did for me with the sumproduct really, but with ticket types.

the first train 0551 is highlighted red on the int data sheet, all with TType of 1, so the summary sheet would show AC1 as 4.

then the next train at 0621 would show 6,1 & 3.

am i making sense?

the ticket type total should equal the No of ints total.

ignore the formula i've used because the more i mess with it the less likely it is to work.

is there a better way?

Re Post #14

Is this the same question?
http://www.excelforum.com/excel-gene...te-totals.html

perfect. i knew it must be a combined sumproduct but just couldnt figure it out.

can you talk me through the formula so i can understand what each part is doing?

Hey romper,

been cracking on with this, and what you've given me is a massive help, never used sumproduct before.

what I was wondering is can you extend the range to say all of coulumn D? rather than at the moment its looking at cells $D$2:$D$227, when i try it it doesnt work.

Hi Romper,

I used your advice and just increased the range.

I’m pretty much finished now bar the odd tweak so thanks again. However, it seems to be taking a very long time to calculate cells, which isn’t ideal, I’m wondering if some of the formulas I have used are too cumbersome?

Can you have a goosies and let me know what you think might be slowing it down so much?

Cheers