#REF Error After Macro

[geospatial]

I have a dynamic range, but after running a macro I end up getting a #REF error. My dynamic range before the formula looks like this =offset(Master!$A$2,0,0,counta(Master!$A:$A),1), and then after the macro it is =offset(Master!#REF!,0,0,counta(Master!#REF),1). The macro ran fine with no problems, but not sure how to keep it from doing this every time.

 

[njimack]

No, you'd have to use a combination of End(xlToRight) and End(xlDown)

 

[geospatial]

Seems like everything is working great now. I do have one last question as I would like to update the formula a bit for a few cells by adding in another variable.

So we currently have =SUMPRODUCT(--(category="Found & Cleared"),--(MONTH)(month)=1)).

I also have a dynamic named range called "Injuries" that has a number in it depending on the amount of injuries there were in an incidient. So what I need to be able to do for this cell is for all rows that contain the Found & Cleared in the Category range and that happened in January from the month range then add the numbers together from the Injuries "Range")

Not sure if it would be easier to do and if or what on this?

 

[Andrew Poulsom]

Try:

=SUMPRODUCT(--(category="Found & Cleared"),--(MONTH(month)=1),Injuries)

 

[geospatial]

Try:

=SUMPRODUCT(--(category="Found & Cleared"),--(MONTH(month)=1),Injuries)

Thanks,

I figured there was an easier way. I was going to do =sumifs(Injuries,--(category="Found & Cleared"),--(MONTH(month)=1))

 

[geospatial]

This is the last question on this thread, but I have noticed one problem when i am totaling certain text searches. In my category range there are the categories direct fire, indirect fire, direct fire threat, and indirect fire threat. When I do =sumproduct(--(category="Direct Fire"),--(MONTH)(month)=1). It ends up adding all 4 categories mentioned above. So what I need is how to find exact text matches.

I tried =sumproduct(--(EXACT(category="Direct Fire"),--(MONTH)(month)=1)), but that did not work. Im guessing its because its wanting to compare 2 sets of text.

 

[Andrew Poulsom]

That's not really your formula is it? For mme it returns #REF! This worked for me:

=SUMPRODUCT(--(category="Direct Fire"),--(MONTH(month)=1))

 

[geospatial]

=SUMPRODUCT(--(category="Direct Fire"),--(MONTH(month)=1)) <-- Is what I am using now, but it is counting Indirect Fire also, were I want it to only count Direct Fire. I was just trying the formula with exact but it did not work.

That's not really your formula is it? For mme it returns #REF! This worked for me:

=SUMPRODUCT(--(category="Direct Fire"),--(MONTH(month)=1))

 

[geospatial]

Scratch that. for some reason my pivot table was not counting the numbers right. I was looking at it to compare the two numbers. When I did my own filtering everything was perfect.

Appreciate the help.

 

[geospatial]

I have come up with a new idea, but it is not working properly. I am sure it is because the formula is pretty butchered. I am wanting to use the below formula but to add more to it that would calculate the numbers for the last 7 days starting from the previous days date. For instance I would like it to count the number of times Direct Fire is found in the category dynamic range for April 10 minus 7 days: What I usually use to auto pull yesterdays date is =today()-1, but not sure if thats the way to go and then how to go back 7 more days from that.

=SUMPRODUCT(--(category="Direct Fire")