Countif Cell equals Active Worsheet

[tcnt9176]

Hello. I need to enter a countif formula if a cell equals the name of the active worksheet and also cell A1. My worksheets are not always the same and I also want to be able to copy this formula to multiple worksheets so if the formula states active worksheet then is should work properly when I past it to multiple tabs (at least in my head).
Thanks. Chris

 

[PATSYS]

I assume that the criteria is a truncation of sheetname and the value in A1.

Assuming that your countif range is A2:A100

=COUNTIF($A$2:$A$100,RIGHT(CELL("filename"),LEN(CELL("filename"))-SEARCH("]",CELL("filename"),1))&$A$1)

 

[tcnt9176]

You will have to forgive me. I am still very limited in my knowledge of VBA and I am trying to make this work in my head. I probably did not explain it the best either. I need to count any line if it matches A1 & if E1 equals the name of the active sheet. Column E is YYYY-MM and I have multiple tabs with different months. I was doing a little searching and put together the following but of course it didn't work.

=SUMPRODUCT(--('Placement Detail'!C:C=A3),--('Placement Detail'!E:E=ActiveSheet.Name))

 

[PATSYS]

Try:

=SUMPRODUCT(--('Placement Detail'!C1:C1000=A1),--('Placement Detail'!E1:E1000=RIGHT(CELL("filename"),LEN(CELL("filename"))-SEARCH("]",CELL("filename"),1))))

 

[tcnt9176]

AWESOME!!! Exactly what i needed. How should this formula be altered to get the sum of these same lines? Thank you very much!!!
Chris

 

[PATSYS]

AWESOME!!! Exactly what i needed. How should this formula be altered to get the sum of these same lines? Thank you very much!!!
Chris

=SUMPRODUCT(--('Placement Detail'!C1:C1000),--('Placement Detail'!C1:C1000=A1),--('Placement Detail'!E1:E1000=RIGHT(CELL("filename"),LEN(CELL("filename"))-SEARCH("]",CELL("filename"),1))))

 

[tcnt9176]

I'm sorry. I should have told you that the $$ that need to be summed are in column F. How do I have it add up column F when it meets the conditions of the formula above? This should be my last question Thanks!!!

 

[PATSYS]

I'm sorry. I should have told you that the $$ that need to be summed are in column F. How do I have it add up column F when it meets the conditions of the formula above? This should be my last question Thanks!!!

=SUMPRODUCT(('Placement Detail'!F1:F1000),--('Placement Detail'!C1:C1000=A1),--('Placement Detail'!E1:E1000=RIGHT(CELL("filename"),LEN(CELL("filename"))-SEARCH("]",CELL("filename"),1))))

 

[tcnt9176]

It worked perfectly!!! Thanks again for your help!!! One of these days I would like to take a course on this stuff. Any suggestions? This way I don't have to bother you & others on here.

 

[tcnt9176]

Ok. I am now working in a different area of the spreadsheet and I was trying to use the format above and I cannot get the formula to work. Can someone tell me what is wrong with the formula?

ActiveCell.FormulaR1C1 = _
"=SUMPRODUCT((--('Placement Detail'!C[10]=RC[-1]),--('Placement Detail'!C=9999)) + SUMPRODUCT(--('Placement Detail'!C[10]=RC[-1]),--('Placement Detail'!C=9000)) _
--('Placement Detail'!R[-2]C[2]:R[9997]C[2]=RIGHT(CELL(""filename""),LEN(CELL(""filename""))-SEARCH(""]"",CELL(""filename""),1)))))"