Using function to compare entries

**NewbieOfVBA** · 07-20-2012, 12:35 PM

Hello,

I currently have this function: =COUNTIF(North!D:D,">="&C3)-COUNTIF(North!D:D,">"&C4)
what this does is it counts how many dates in a worksheet called "North" are between the dates in cells C3 and C4.

The problem i'm having is, i now need to create a function that will look at column C (that has pump names in it) in worksheet "North", and if it finds the same pump in worksheet named "NorthEquip", it will count it, and then give me that total in a different column.

Thanks!

**NBVC** · 07-20-2012, 12:38 PM

Do you mean like?

=COUNTIFS(North!C:C,NorthEquip!$A$2,North!D:D,">="&C3,North!D:D,"<="&C4)

assuming you have Excel2007 as per your profile. And assumes item to find in column C is in A2 of NorthEquip.

for pre 2007:

=Sumproduct(--(North!C1:C100=NorthEquip!$A$2),--(North!D1:D100>=C3),--(North!D1:D100<=C4))

note, can't use whole columns...

**NewbieOfVBA** · 07-20-2012, 01:01 PM

hello thanks for replying.

i used the formula you gave (i'm a 2007). and there are 3 pumps in column c that match the pump number in A2, but it only gave me a total of 2. So i'm not sure what the problem is.

I am overall wanting this function to give me a count of how many entries there are that fall inbetween 2 dates, AND are found in the sheet "NorthEquip".

**NBVC** · 07-20-2012, 02:22 PM

What do you mean by "are found in sheet "NorthEquip"? Are you comparing to a range of cells, and checking if any one is matched?

If so:

=SUMPRODUCT(COUNTIFS(North!C:C,NorthEquip!$A$2:$A$10,North!D:D,">="&C3,North!D:D,"<="&C4) )