LinkBack URL
About LinkBacks
I've attached a sheet with the desired outcome that I'm looking for as its hard to describe.
I think its using Sum and IF function but I can't seem to get the range correct.
I've attached a sheet with the desired outcome that I'm looking for as its hard to describe.
I think its using Sum and IF function but I can't seem to get the range correct.
In cell D13:
Formula:
Please Login or Register to view this content.
Copy down as needed - but be careful, some of your cells contain spaces at the end of the text...
let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source
If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE
Walking the tightrope between genius and eejit...
Hi,
For a start, you have a very inconsistent data set - extra spacing at the end of words ("Compliant " in some cases, "Compliant" in others), different spellings/syntax (Non Compliant/non-compliant) which will need to be cleaned up for a reasonable solution to work.
Secondly, I'm not sure how you arrive at your desired totals: I can see that there are 17 entries for Compliant in the entire grid, yet only 5 of these correspond to an entry in C4:C8 of Blue - can you please clarify?
Regards
Last edited by XOR LX; 11-11-2013 at 09:18 AM.
hi dowell. you could actually do with:
=COUNTIF($E$4:$M$8,C13)
but your data is not very clean.
lots of them have extra spaces. Action required in C15 has no spaces. Action Required in D5 has a space behind
"Non compliant" without dash in C14, but the 4 of the cells in E4:M8 has dashes
if your data is really that unclean & you want to match those without dashes too, then maybe:
=SUMPRODUCT(--(TRIM(SUBSTITUTE($E$4:$M$8,"-"," "))=TRIM(C13)))
Edit: according to colour would be:
=SUMPRODUCT((TRIM(SUBSTITUTE($E$4:$M$8,"-"," "))=TRIM(C13))*(TRIM($C$4:$C$8)=LEFT($C$12,FIND(" ",$C$12)-1)))
Last edited by benishiryo; 11-11-2013 at 09:11 AM. Reason: additional info
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
I've just realised I put the wrong figures on that upload. Sorry. I need the total of the outcome just for specific systems. I've uploaded the correct sheet now
i second what XOR LX says confused
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Hi,
try this if you want the results just for 'Blue',
D13: =SUMPRODUCT(($C$4:$C$8="Blue")*($E$4:$M$8=C13))
...and copy the formula down into D14, D15 and D16.
Will work only if the text in C13 to C16 exactly matches the text in E4:M8.
Cheer
<-- If you're happy & you know it...click the star.:-)
Also Sorry I know that the date is messy. I just had to make up a quick representation of the tables I'm using. My actual spread sheet can't really be uploaded on to this forum.
I will go through and clean it up before the final formula is put in.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks