Hello Folks,
I'm looking for a little help with the following formula.
=SUMPRODUCT((Data!$A$2:$A$10000>="01/01/14")*(Data!$A$2:$A$10000<="01/31/14")*(Data!$I$2:$I$10000="Bulk")*(Data!$K$2:$K$10000=$A3)*(Data!$P$2:$P$10000=$B3)*(Data!$Q$2:$Q$10000=$C3))
Do I need to add datevalue to my formula? Or format it differently?
Its just giving me a 0 result right now but it should be 7 instances.
yeah, it's reading "01/01/14" literally as a text string, not a date
Try using "01/01/14"+0
You might also try
=SUMPRODUCT((TEXT(Data!$A$2:$A$10000,"mmyy")="0114")*(Data!$I$2:$I$10000="Bulk")*(Data!$K$2:$K$10000=$A3)*(Data!$P$2:$P$10000=$B3)*(Data!$Q$2:$Q$10000=$C3))
Use cells to hold the dates then refer to those cells.
What version of Excel are you using?
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
hi there. like tony asked, what is the version you are using. it helps us give you newer & more efficient formulas. you could use COUNTIFS for eg if you have Excel 2007 & above:
Formula:
Please Login or Register to view this content.
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks