Trying to create total numbers if certain criteria is met

Please Login or Register  to view this content.

countif will do the job for you.

But why not a pivot table, they made for this kind of work?

Not sure where you got the values from in your table, nor which column you want to test teh dates against, but try this, copied down and across...
=COUNTIFS(NameTab1!$B$2:$B$139,'OUTPUT - Numbers'!$A3&"*",NameTab1!$E$2:$E$139,">="&'OUTPUT - Numbers'!B$2,NameTab1!$E$2:$E$139,"<"&EDATE('OUTPUT - Numbers'!B$2,1))

You may need to change teh date columns

Oeldere - I haven't used them and the column dates will change due to the need for a 12 month rolling calendar. Is a pivot table going to help with that as well?

FDibbins - Thanks. Let me see if that works. The dates are tested against the current month and 4 months prior to ensure that shots are done after the 4 months timeframe. I probably won't use 4 months out since a year is sufficient time but I needed a shorter window to see if it works.

If my question is resolved, I'll change to solved using the thread tools.

Thanks again.

Hey FDibbins,

I don't see where the number count was done for all cases where 4-months timeframe had lapsed from the month in row 2 of the OUTPUT - Numbers. Hence, why I have 0 in the AET row but 1 in the VED row. There was only one case in VED that didn't get shots from the 4 month timeframe from the January month. Does this make sense or do you need more clarification?

Please let me know since the countifs I have used before and would like to continue using.

Thanks.

Rick

This can be done with an pivot-table, but you have to tell us how the manual added desired result, is related to the date.

I tried several options, but don't come to the same result.

see the attached file with an pivot table.

You have to tell us the criteria.

Thanks oeldre. This is getting close to it but I want to lump in AET into one line, and same for the REF and VED.

I'm not sure what you mean by criteria which I have given several times. I want to know in January how many AET, REF, or VED animals haven't had their shots based on the "Last Shot Date (column E)" in more than 4 months time, then February as well, and such.

As for the pivot table, I'm not sure it will work since AET-Cat, AET-Dog, and such are not lumped together into the AET or such.

Is there a way to do a formula that takes the date filed minus 4 months prior and with the Name (first part before the dash)?

Rick

This solves the problem wiht the names.

=left(b2,3)

See the attached file.

Thanks oeldere but reviewing the new pivot table isn't correct. If January had 4 cases for AET animals haven't had their shots in the last 4 months, then February should be January numbers plus February's. Maybe I need to repost to with COUNTIFS section of this.

Another try.

Based on the data in column E (last shot).

als cummalitive data.

See the attached file.