Hello,
I need a little help from an expert,
I have a speadsheet with ID numbers and dates where i wanted to count the number of occasions this is true in a year only.
so i had this little formula working perfectly...
=COUNTIFS($B$4:$B$1504, B4, $G$4:$G$1504, ">" & TODAY()-365)
and i converted this to this for Excel 2003...
=SUMPRODUCT(--(B$4:$B$1504=B4),--($G$4:$G$1504>TODAY()-365))
and now im trying to convert this
=SUMIFS($J$4:$J$1504, $B$4:$B$1504, B4, $G$4:$G$1504, ">" & TODAY()-365)
to excel 2003 compatible
but this one is harder to accomplish.. i am trying to add values together in cells J4:J1504 only the ones where the value of B4 matches the value in Cells B4:B1504, it should also look at Cells G4: G1504 where these contain dates, if the date is older than a year, it should not be counted... So you see?
Can you help?
Thanks
Try this:
=SUMPRODUCT(($B$4:$B$1504=$C$1)*($G$4:$G$1504>TODAY()-365)*$J$4:$J$1504)
Click the star icon in left-corner of my post if you find my post userful!
Quang PT
quangphanidico@yahoo.com
PM me: Y!M: quangphanidico
Thanks for that...
It returns a #Value!
any other ideas?
Try to follow my example in attachment.
Click the star icon in left-corner of my post if you find my post userful!
Quang PT
quangphanidico@yahoo.com
PM me: Y!M: quangphanidico
any chance you can put that in to a format that can be read by excel 2003?
I attach again in 2003 format.Originally Posted by C3467
Some case with 2007+ formula will not active.
Click the star icon in left-corner of my post if you find my post userful!
Quang PT
quangphanidico@yahoo.com
PM me: Y!M: quangphanidico
Ok i see that from your formulas on your sheet, it works... not a problem
When i try to change it and apply this to mine, it comes up with #Value!
This is the formula i have devised from your example...
=SUMPRODUCT(($B$4:$B$1504=B4)*($G$4:$G$1504>(TODAY()-365))*$J$4:$J$1504)
am i missing something?
It works for me. If thing still goes wrong, try to attach your example.
Click the star icon in left-corner of my post if you find my post userful!
Quang PT
quangphanidico@yahoo.com
PM me: Y!M: quangphanidico
ok, i am at work at the moment and i am having problems with uploading my project, so i will upload in 2 hours time...when i am home..
here we go.... i had to cut the rest of it from lines below as it was too large....
see if you tell what is wrong in cell m4
edit: Now that i have uploaded this and checked - its working... But this is becuase i have Excel 2010 at home, i need this to work on 2003 at work... Clearly the Formula as it is in M3 isnt working for Excel 2003... I just dont get the incompatability!
Can you help further?
Last edited by C3467; 02-05-2012 at 02:19 AM.
I dont have 2003 version and I couldnt check, but I am sure that SUMPRODUCT works for 2003.
May be you could check again at work with 2003.
Click the star icon in left-corner of my post if you find my post userful!
Quang PT
quangphanidico@yahoo.com
PM me: Y!M: quangphanidico
Is there an alternative to using SumProduct?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks