Solved Count formula using filtered data

March 2, 2018 at 08:31:21
Specs: Windows 7
Hello forum I am trying to get a count of values that meet the criteria of dates that have expired or will expire in the next 6 days but on filtered data, the data is in column AF as date format.

I tried my hand witht his formula but can't get it to work:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(AF10:AF500,ROW(AF10:AF500)-MIN(ROW(AF10:AF500)),,1)),--(AF10:AF500="<"& TODAY()+6))

I also tried which is similiar but also did not work:
=SUMPRODUCT((AF10:AF500="<" &TODAY()+6))*(SUBTOTAL(103,OFFSET(AF10,ROW(AF10:AF500)-MIN(ROW(AF10:AF500)),0)))

Can anyone help me tweak it to work?

message edited by mecerrato


See More: Count formula using filtered data

Report •

#1
March 4, 2018 at 08:26:37
✔ Best Answer
After some searching and some help from mrexcel.com I was able to locate the syntax issue and would like to provide in case anyone else rungs into this; the issue was in the section AF500="<" &TODAY()+6, I had to rewrite it so it looks like the solution below:

Working formula:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(Pipeline!AF10:AF500,ROW(Pipeline!AF10:AF500)-MIN(ROW(Pipeline!AF10:AF500)),,1)),--(Pipeline!AF10:AF500<TODAY()+6))


Report •
Related Solutions


Ask Question