• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

COUNTIF [SOLVED]

B

brems

Member
Hello to all,


I want to count a certain value on different tabs. Therefor I started to determine to the range within the formula needs to look.

Starting point was determined with the formulas MATCH and ADDRESS. This resulted in cel B6 as the starting point of my range. Then I worked the same way to determine the last cell of the range. This resulted in cell AF6


So the range to look in is: B6:AF6


Starting point:

=ADDRESS((MATCH(huidigemaand;INDIRECT("'"&D6&"'"&"!A3:A14");0)+2);2;4)

Ending point:

=ADDRESS((MATCH(huidigemaand;INDIRECT("'"&D6&"'"&"!A3:A14");0)+2);COLUMN(AF1);4)


Then I had the idea of making a formula like this:

COUNTIF(Starting point:Ending point;"condition") or COUNTIF(B6:AF6);"condition")


I'm probably complicating things to much but this is the (not working) solution I came up with. When I try the formule above, Excel doesn't accept it. Am I overlooking something?


Thanks for your help.

Regards

Wim
 
Good day Wim


A sample file may would help.


http://chandoo.org/forums/topic/posting-a-sample-workbook
 
Hi bobhc,


Excellent idea!

I posted a sample file on the following link.


https://docs.google.com/spreadsheet/ccc?key=0Av-NhSp9ULywdEtzWkN3YzJNX2RFd3ZoUHlOLUN4ZkE&usp=sharing


I've seen with the upload in google docs that some formulas stopped working.

The begin cell for the range is on tab "overzicht" J6 and the ending is in K6.

I want to know the number of times the value "VAK" and "Z" appears on each tab per line (=month)


Please let me know if you need complementary information.

Many thanks in advance


Regards

Wim
 
Hi Wim ,


I am not sure whether this is what you are looking for :


=COUNTIF(INDEX(INDIRECT("'"&$G5&"'"&"!$B$3:$AF$14"),MATCH(huidigemaand,INDIRECT("'"&$G5&"'"&"!$A:$A"),0)-2,0),"VAK")


Changing the last parameter to "Z" will give the corresponding count for Z.


Replace the commas by semi-colons for your system.


Narayan
 
Hi Narayan,


This solution works excellent! It seemed that I was complicating things to much... :))

Thank you again for your assistance.


Much appreciated.


Regards

Wim
 
You must log in or register to reply here.
Back
Top