COUNTIF using current column

[Shaun07776]

Hi,

I have the following formula which works fine but i need to do this serveral times with different text

=COUNTIF($C$3:$C$37,$AN8)

is it possible to do something like

=COUNTIF($COLUMN()$3:$COLUMN()$37,$AN8)

Thanks in advance

[BadlySpelledBuoy]

Hi Shaun07776 and welcome.

Do you mean you're going to drag the formula across the worksheet and you want the column to increment each time?
If so, remove the $'s on the column references.

Please Login or Register  to view this content.

BSB

[protonLeah]

You say you are doing this several times, so where does the count go? To the same cell each time or different cells?

[Shaun07776]

So i have a calendar which is counting how many evenings and weekends the engineers do.

so i just need to count all the weekdays that have say SH in for the current column

[BadlySpelledBuoy]

Probably easier to provide a solution if we could see the workbook. Any chance of you posting a desensitized sample one?

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

BSB

[Shaun07776]

Well Turns out i had not thought it through.

i've come up with this which is working, is there a better way to write it?

=SUM((COUNTIF($C$3:$C$7,INDIRECT(ADDRESS(ROW(),40,4)))),(COUNTIF($C$10:$C$14,INDIRECT(ADDRESS(ROW(),40,4)))),(COUNTIF($C$17:$C$21,INDIRECT(ADDRESS(ROW(),40,4)))),(COUNTIF($C$24:$C$28,INDIRECT(ADDRESS(ROW(),40,4)))),(COUNTIF($C$31:$C$35,INDIRECT(ADDRESS(ROW(),40,4)))),(COUNTIF($C$38:$C$42,INDIRECT(ADDRESS(ROW(),40,4)))))

Thanks

[BadlySpelledBuoy]

is there a better way to write it?

Hard to say without seeing a sample workbook so we can see exactly what you're trying to accomplish.

BSB

[Shaun07776]

Hi,

I've attached an example

[BadlySpelledBuoy]

I assume you're working on a formula for cell AO4 and below.
Rather than all that cumbersome INDIRECT and ADDRESS stuff, you could amend your formula with the parts in red below:

Please Login or Register  to view this content.

But if what you're doing are counting the instances of each value in AN4:AN8 that do not fall on weekends then the below entered into cell AO4 and copied down should do the trick.

Please Login or Register  to view this content.

You can copy that to subsequent months and amend the reference to column C to suit.
So Feb would be

Please Login or Register  to view this content.

Is any of that of use to you?

BSB

[Shaun07776]

Hi BSB

Yes that works perfect thanks

i'm pretty sure i was complicating it it didn't help that i inherited the spreadsheet, its going to be a lot easier now

Thanks

[BadlySpelledBuoy]

You will still need to deal with the bank holidays, but that shouldn't be too onerous.

Feel free to mark the thread as SOLVED if you're happy you have a working solution

BSB

[Shaun07776]

Yes the bank holidays i calculate separately, be nice to add them twice if there was some marker on that cell or if the formatting was a set way is that possible way.

When i can work it out i'll mark it a solved as this is really a separate question

[BadlySpelledBuoy]

You could just set up some cells to sum the bank holiday values for each month then subtract those from the relevant monthly totals.
Not sure what I mean by that it clear. Have to disappear shortly but will be back later and will try to put together something to demonstrate.

Your data layout doesn't make this a simple process for subsequent years to be automated I'm afraid. Certainly not without VBA.

BSB