Replies

Thank you Stuart and Jan – that did the trick!

Thanks – that did the trick!

No problem and thank you. Both worked perfectly.

Scott

Thanks Hans. That didn’t seem to work. Only $0.00 was returned. I tried it in a couple of different worksheets, with the same result.

Hi Hans,

I took a look at it again. What I didn’t mention in my original post as that I was doing a the same wildcard search in two different columns. When I used Like”*PFPC*” I placed it in two separate Criteria rows. I thought the same logic applied when using Not; however, when I placed Not Like”*PFPC*” on the same Criteria row under each column, the results were returned correctly.

Thanks,

Scott

Thanks Steve, Hans, et al.

I went back to my date column just to make sure that all the cells were set to the same format, and sure enough, that’s were the problem was. After I set all cells to the same format the #VALUE! vanished and the correct SUM’d value appeared.

Thanks for all of your help guys – I appreciate it.

Thanks Steve. I did do ctrl-shift-enter, so I’m not sure why. I think what I’ll do is add an additional column, one for statement date and the other for item date, which should work fine. Thanks again for your help.

Thanks Steve,

Hmmm…..both returned a #VALUE! error – not sure why. It does need to be genereric as row(s) will be added each month with new entries.

I am getting closer with this formula, =SUMIF(B2:B52,MAX(B2:B52),G2:G52) , however, for it to work properly, all of the dates must be the same.

Thanks Hans! It worked great! Appreciate your help.

Scott

Worked great. Thanks Hans.

Thanks Hans for the explaination and the solution – it worked great. I’m kicking around the idea of updating this field dynamically by using an IIf statement in the control source of the field. If I do and get stuck, I’ll probably submit another question.

Thanks for your help.

Scott

Thanks Winston. I couldn’t get it to work, i.e. still prompted for dates twice, but I suspect I know why. I forgot to mention that another query sits on top of the QUARTERLY_EXPORTqry query (the query to be exported to Excel), which aggregates data from the first query. It is in this first query that the between start date and end date criteria is set, which may explain why I’m still being prompted twice. At any rate, I’m going to create a separate database to give this a try with some temp data to at least see if I can get it to work.

Thanks for your help.

Thanks Hans. That worked perfectly.

Thanks Wendell. It worked great!

The rounding formula worked! Thanks for your help!