COUNTIF [SOLVED]

[brems]

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

 

[bobhc]

Good day Wim


A sample file may would help.


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

 

[brems]

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

 

[NARAYANK991]

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

 

[brems]

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