VBA Count Ifs cell in criteria range is not equal to cell below

[aek919]

Hi,

I don't know if this is actually possible, but I'm hoping it is!

First of all, does COUNTIFS do so in order?
So if the second criteria listed is not there it stops? Or is it an all or nothing thing to use this formula?


I want the row to be counted if:

1. Criteria Range: Column F = “Fungicide”
   
   
2. Criteria Range: Column D = “Greens”
   
3. Criteria Range: Column AA = year specified by user and saved as “strYear”

Then finally,

1. Criteria Range: Column A = date in cell in current counting row does not equal the date in the row below (ActiveCell <> ActiveCell.Offset(1,0)) – BTW this didn’t work, but this is the concept that I’m persuing.
   

But a crucial point is that I only want it to check the 4^th criteria, if the first three are met, and only if the row below it has also met that criteria.


Also I need to do this about 20 times from the click of one button, so I'm hoping that it can be a simple cut and paste.


This is quite complicated and sadly is only necessary in rare cases, but if the count is wrong it will throw off a lot down the road.



Thanks, Amy

 

[mole999]

IF would produce the first value from a set of choices that is true
VLOOKUP would find the first value that is true from a sorted list

I think COUNTIF is about gathering the total of all cells that match your criteria

for your 4th you would define a AND query to make sure your first three are right

 

[aek919]

IF would produce the first value from a set of choices that is true
VLOOKUP would find the first value that is true from a sorted list

I think COUNTIF is about gathering the total of all cells that match your criteria

for your 4th you would define a AND query to make sure your first three are right

Would I set it up as a Do Loop to go thru the database?

I'm really confused how to set this up.

This is part of my code right now...None of it working, and currently all as notes in VBA.

'Fungicides
'Number of Applications & Greens

Sheets("Application Database").Select
Range("A4").Select
'.Range("B17") = Application.WorksheetFunction.If((AppDBTypeRange = "Fungicide") AND (AppDBTargetAreaRange = "Greens") AND (AppDBYearRange = strYear) Then
' CountIf

Do Until ActiveCell = ""
'If ((AppDBTypeRange = "Fungicide") And (AppDBTargetAreaRange = "Greens") And (AppDBYearRange = strYear)) Then
'COUNTIF (AppDBDateRange <> ActiveCell.Offset(1,0)
Loop


'Still need to find out how to not count the ones with the same date.


' not equal VBA code is <>

'COUNTIF -- B17
'Product Type = Fungicides
'Target Area = Greens
'Year = Year Specified by User
'Active Cell (in column A) does not equal ActiveCell.Offset(1,0)

'Answer should be 3