how to countifs to count till the colored row

[narsing18]

Hi ,

I am looking for a formula which count the number of rows between header and the colored row.

for example my header is B1 and the colored row ( Blue) is at B10 , how to count number of row between header and Blue colour.

is it possible??

[AliGW]

What, apart from the blue colour, sets this row apart? If it is just the colour, then you will need a VBA solution. Please confirm.

[Pete_UK]

Is the colour applied manually, or is it through some conditional formatting? It would help if you attached a sample Excel workbook.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Please note that the Paperclip icon does not work.

Pete

[narsing18]

Hi ,

I am looking for a formula which like below.

=COUNTIFS('Calls breaching in 5 days'!$H$3:$H$247,"Sev-1") here the formula counts for sev-1.

here $H$247 should be till some coloure not the row count.

hope you got it.

Thanks

[Glenn Kennedy]

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[narsing18]

Hi Please find the attachment.

in this i have 2 sheets one is "summary" and other is "Calls closing in 5 days" sheet

i have macro which calculate and paste the data in "Calls closing 5 days".
first it will paste "Open with customer" and leaving two line paste "Calls with BB"
in summary sheet i am Calculating count of Sev-1 ,Sev-2 ,Sev-3,Sev-4.


=COUNTIFS('Calls breaching in 5 days'!$H$3:$H$6,"Sev-1")like i have formulas in all cell,my problem is my data is not fixed , row count keep changing daily.

Open with customer = row count daily increase and decrease that's why i am trying to give range until row is filled with blue color.

Calls with BB = Row count should start after blue color end until data is available

Thanks

[Pete_UK]

Will the "Open with Customer" table always start on row 1 of the "Calls breaching in 5 days" sheet, and then continue for however many rows are needed and then be followed by 2 blank rows before the "Open with BB" table starts?

Pete

[Pete_UK]

I've assumed the answer to those questions is "yes", so you can put this formula in cell H9 of the Summary sheet:

=COUNTIFS('Calls Breaching in 5 days'!$E$3:INDEX('Calls Breaching in 5 days'!$E:$E,MATCH("Open with BB",'Calls Breaching in 5 days'!$E:$E,0)-3),H$8)

and this one in H10:

=COUNTIFS(INDEX('Calls Breaching in 5 days'!$E:$E,MATCH("Open with BB",'Calls Breaching in 5 days'!$E:$E,0)+2):'Calls Breaching in 5 days'!$E$100,H$8)

then copy both formula across to column K.

Hope this helps.

Pete

[narsing18]

To look good first row "Open with Customer" i have colored ( Orange) it but actual report start with copying header at row 2 and in the same way

leaving 2 rows it will copy the "Open with BB" above it i have inserted colored row to look good.

[Pete_UK]

Forget about the colours. My formulae in Post #8 both look for the text "Open with BB" in column E and use that row to determine the range to look through - either from E3 to 3 rows above where it occurs, or from 2 rows below where it occurs down to E100 (arbitrary).

Hope this helps.

Pete

[narsing18]

I never used INDEX formula....i hope H9 formula will calculate 3 lines above h10 and it will dynamically adjust row count for "Open with customer"?

[Pete_UK]

I never used INDEX formula...

Well, hopefully you come along here to learn about new functions. Put this part of the formula in a blank cell somewhere:

=MATCH("Open with BB",'Calls Breaching in 5 days'!$E:$E,0)-3

it should return a (row) number which is 3 less than the row where "Open with BB" occurs in column E. Insert a few blank rows above that row in the Calls Breaching sheet, and you should see it adjust.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

[avk]

Dear narsing, While going through your sheets, I obsered that, if in future data row increase then how to change every time (i mean insert row).
I suggest you one simple thing if you comfirtable you impletement.
To maintain data "Open_with_Customer" in sheet Open_with_Customer & data "Open_with_BB" in separate sheet : Open_with_BB
In above both sheet insert "table" (In this case if you add data in future automatically calculate data in summary sheet.
In summary "B2" use formula

Please Login or Register  to view this content.

In "B3" use formula

Please Login or Register  to view this content.

Look attach file. I think this is more easy & comfirmtable. Yes it is upto you which is use.

[narsing18]

Hi Pete,

This solves my problem right now and what you have suggested is applicable for me, i may come back to you for some more changes if required in a week time.

Thanks for the Knowledge you have shared.

Regards,
Narsing Rao

[narsing18]

Hi AVK,

What you have shared more simple approach and it will work perfectly without creating any errors, but my management has created this sheet long back and following the same summary sheet . they dont want to chane any think just now i have wrote a VBA code to automate other wise they are doing it manually sinc long.

Thanks for your approach .

[avk]

Hello Narsing, If changes are good & works perfectly with easy method, why not management accepted changes. Convey to management, if in future, data row increase frequently in Open_with_Customer & Open_with_BB, its every time to change data structure (inserting row), & as per good practice in excel maintain data of each table in separate sheet, & don't blank any row or column within sheet, because if data increased (in future) the file size goes large.
There are surely many more tips and guidelines that, i did not cover here. I look forward to hearing other suggestions and feedback from you.
Also avoid to merging cells. (This is not in your file, but this is for your information purpose).

Secondly, anyone responsible person can handle this file, in your absent.

[narsing18]

Hi AVK,

The file which i have shared actually is a Replica of large data base we have this is a small peace i have shared for sample purpose. but i am sure that in future i can convince and make changes , we have some other reports which have same structured what you have suggested but this particular sheet they want it in single sheet for easy comparison.

Regards,
Narsing Rao

[avk]

Ok Narsing, good wishes for future.
Plz note :If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

[narsing18]

Hi Pete,

I am trying to apply the same formula to my original sheet were Severity column is H and D29 is the cell number for Sev 1 in Summary sheet and the sheet name is "Calls breaching in 48 hrs"

in attached excel sheet the only change is now Severity column is H and the sheet name is "Calls breaching in 48 hrs".

the next data has 2 rows gap in between them and the heading is "Open with BB" with header name as severity

=COUNTIFS('Calls breaching in 48 hrs'!$H$3:INDEX('Calls breaching in 48 hrs'!$H:$H,MATCH("Open with BB",'Calls breaching in 48 hrs'!$H:$H,0)-3),D$29)

i am getting 0 VALUE , but the count should be some number as per the data.

can you please check the formula what went wrong?

[Pete_UK]

I think you forgot to attach this latest file.

Pete

[narsing18]

i am referring to my previous attachment ......but i have solved it for my self...thanks