How do I create a macro from a SUMPRODUCT formula for a range whose number of rows changes

hi bananius,

total = ActiveSheet.Evaluate("SUMPRODUCT(--(MMULT(--(" & Range1.Address & "=" & """VAC""" & "),{1;1;1;1;1;1;1;1;1})<5))")