Currently have the formula =COUNTIFS(Data!$C:$C,E8,Data!G:G,"<0") to return the # of items less than 0. Cell E8 is now a problem because I have an "s" and a "S" to match. How would I update formula to get the exact match? having the same issue with =SUMIFS(Data!$J:$J,Data!$C:$C,E8,Data!G:G,"<0")
Thank you,
Hello and welcome to the forum.
COUNTIFS and SUMIFS are not case sensitive so there much be something else that makes the cells differ (like a trailing space for example).
Ooh I think that I misunderstood your request. Are you saying that s and S should not be seen as the same?
That was my take 63FD, that the count for S and s and the sums for S and s are different and he doesn't want them combined.
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
correct. s will return 105 and S will still return 105 even though S is really 94 or something...For the mean time, i just changed "S" to "S " with a space after to differentiate to lookup. But it's time consuming to change...
s needs to return 105 and S needs to return 94
while contemplating the issue of S and s, if it is a stand alone in the cell a simple find what >> S and replace with what >> S [space] and match entire cell contents and match case check boxes should make it much faster.For the mean time, i just changed "S" to "S " with a space after to differentiate to lookup. But it's time consuming to change
=COUNTIFS(Data!$C:$C,E8,Data!G:G,"<0")
Can be changed to:
=SUMPRODUCT((EXACT(Data!$C$2:$C$100,E8))*(Data!$G$2:$G$100>0))
=SUMIFS(Data!$J:$J,Data!$C:$C,E8,Data!G:G,"<0")
Can be changed to:
=SUMPRODUCT((EXACT(Data!$C$2:$C$100,E8))*(Data!$G$2:$G$100>0)*(Data!$J$2:$J$100))
Just change the ranges to suit your data.
Originally Posted by 63falcondude
works like a charm !!!
I have a few more I can't figure out how to integrate the "exact" formula if you can please assist
=COUNTIF(Data!C:C,A4)
=SUMIFS(Data!$J:$J,Data!$C:$C,A4)
=COUNTIFS(Data!C:C,I4,Data!G:G,">=0",Data!G:G,"<2")
=SUMIFS(Data!$J:$J,Data!$C:$C,I4,Data!G:G,">=0",Data!G:G,"<2")
=COUNTIF(Data!C:C,A4)
would be
=SUMPRODUCT(--(EXACT(Data!C$2:C$100,A4)))
=SUMIFS(Data!$J:$J,Data!$C:$C,A4)
would be
=SUMPRODUCT((EXACT(Data!$C$2:$C$100,A4))*(Data!$J$2:$J$100))
=COUNTIFS(Data!C:C,I4,Data!G:G,">=0",Data!G:G,"<2")
would be
=SUMPRODUCT((EXACT(Data!C$2:C$100,I4))*(Data!G$2:G$100>=0)*(Data!G$2:G$100<2))
=SUMIFS(Data!$J:$J,Data!$C:$C,I4,Data!G:G,">=0",Data!G:G,"<2")
would be
=SUMPRODUCT((EXACT(Data!$C$2:$C$100,I4))*(Data!$G$2:$G$100>=0)*(Data!$G$2:$G$100<2)*(Data!$J$2:$J$100))
Again, making sure to adjust the ranges to fit your data.
Thanks for the rep!
This should give you enough to be able to convert any others that you have yourself.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks