Countif with Merged Cells

[Alexander_Read]

Hi all

As said I have spent three days trying to figure this out and have still not got any closer!

Here is the spreadsheet I am working with:

Example1.zip

I need a formula that will tell me how many times "John" does sales after the 2nd Sep. I.e. on the 3rd to the 7th.

I have tried a countif and sumif combination, but the merged cells cause problems. Please advise.

Many Many Thanks!

Alex

[mudraker]

Please take a couple of minutes and read the Forum Rules then edit your thread title by following the instructions in the rules (Rule 1) so that it better discribes your problem/request

[19fortysix]

Alex,

This is one possible solution. I created a named range "John" with the formula below. Cell N1 contains a date. Cell L2 contains the formula =COUNTIF(John,"Sales") and will return the number of cells under John after the date entered in Cell N1.

=OFFSET(INDIRECT("I"&MATCH(Sheet1!$N$1+1,Sheet1!$B$1:$B$30,0)),0,0,COUNTA(Sheet1!$I:$I),1)

Example1.xls

Hopefully this will be useful.

Gary

[arthurbr]

Alex,
best thing to do is to get rid of merged cells !
Use them for eyecandy or to make your sheets look good, but avoid them at all costs if you want to use them for any analyze.
To have the same effect try format -alignment - select position horizontal and "center across selection".
This will look the same and save you lots of headaches.
Cheers

[19fortysix]

Alex,

Arthur's post is right on...get rid of the merged cells if you can. But if you prefer to keep this here is an easier way than my previous post.

Use a cell somewhere to contain the "count after" date (I used $N$1 in this formula) and then enter this formula wherever you want the count to display.

=COUNTIF(INDIRECT("I"&MATCH($N$1+1,$B$1:$B$30,0)&":I30"),"Sales")

Don't know why I didn't think of this yesterday.

Good luck,
Gary

[shg]

best thing to do is to get rid of merged cells!

I think we are unanimous.

[Alexander_Read]

Hi

Thanks for the input. I will be using those formualae today!

Yes, I agree merged cells are a headache, but sometimes I require them for aesthetic purposes.

I tried the center across selection, but can't get the same effect as a merged cell. It only seems to work with a horizontal selection? How do I achieve the merged effect with a vertical selection?

Regards

Alex

[Alexander_Read]

Hi Gary,

Thanks for the great formula!

I am working on my Excel skills but sometimes you can’t work out a formula and fresh input helps! You also get to learn some neat new tricks!

The formula you gave is perfect! I just need to tweak it.

1) I want to be able to specify a person. I have put a little table together to show you. (I have used a match function to identify “John” as being in column 9, but I’m not sure how to convert 9 to “I”, without doing another table and lookup) (I almost need an inverse of the columns function)

Example1.zip

2) I want to check the dates across the sheets and between two dates. I.e. between the 2nd Sep & 11th Sep in sheets “Week 1” and “Week 2”. (I have five sheets but have only included 2 in the sample)

Regards

Alex