How to sum only numbers in column B that have a corresponding "ü"/Checkmark in Column A

**irnwil** · 08-09-2012, 02:35 PM

New to the community, and seeking answers if they can be found. Hello all! I understand some basics of formulas, but not enough to create my own routinely. I am trying to select from a column of numbers "B" ONLY the numbers that have a corresponding check mark "ü" in the adjacent cell in column "A" then sum only those numbers. I apologize, about the format of the following example, as I am not sure how to make the table work in this forum just yet.

Example:
Column A
ü
Blank
ü
ü
Blank

Column B
10
12
22
05
16

Total: 37

I've tried the SUMIF function:
=SUMIF(A3:A15), "ü", (B3:B15)
which only yeilds: ü ??

Will it work to use some version of an IF/AND/Then statement? Something like?
=if(A3:A25),<>,AND(B3:B25)="ü",SUM(B3:B25)

Neither of these ideas seem to address my need to select only those numbers which have a check mark next to them and give a total of only those numbers. There might also be a way to copy those numbers into a separate list, sum that list, and return the value I need. I appreciate the help of anyone who can crack this simple task. Thanks!

**TMS** · 08-09-2012, 02:42 PM

=SUMIF(A3:A15, "ü", B3:B15)

Regards, TMS

**irnwil** · 08-09-2012, 02:50 PM

Thanks TMS, but this doesn't seem to work. I just revised my parenthesis to resemble yours and the output is: ü instead of a numerical value.

**ChemistB** · 08-09-2012, 02:54 PM

TMS's formula should work.
=SUMIF(A3:A15, "ü", B3:B15)

Can you upload the spreadsheet? (Go Advanced>Manage Attachments)

**irnwil** · 08-09-2012, 03:20 PM

Thank You ChemistB.

I have attached the portion of my spreadsheet that includes the formula:

=SUMIF(E2:E24,"ü",F2:F24)

Essentially, I am trying to add up only those values associated with the check marks in column E.

**tridom** · 08-09-2012, 03:25 PM

Try =SUMIF(E2:E24,"<>",F2:F24)

**TMS** · 08-09-2012, 03:34 PM

Well, two things: you have a custom format which will probably just show the "ü". Change the format to General ... wherever you want to use the Wingding format.

Then the result is 10 ... which doesn't look right, but is. Only the cell E2 has the "ü" in it. Others are displayed as "ü" but actually have an "X" in them. Or the corresponding values in column F have question marks in them.

So, nothing wrong with my formula ... it's mainly down to the cell format or the cell content, which you can't see because of the cell format.

Regards, TMS

**ChemistB** · 08-09-2012, 03:37 PM

Tridom's solution is a good one.
Here's what's happening. The spreadsheet is set up such that no matter what is entered in B2:H24, a ü is displayed (in wingding font, shows as a check mark). However, if you entered an x, it's still an x even though it looks like a checkmark. Tridom's solution works if there's anything in column E range, sum the col F range.
You could also modify your spreadsheet so that you have to enter an ü. Hope that helps.

Oh, and lastly, you have the format in cell E25 set up the same way so that no matter what the result, it will show up as ü You want to change that to General.

Lastly, again

, this is definitely one we wouldn't have been able to figure out without you attaching your workbook.

**TMS** · 08-09-2012, 03:38 PM

Note: most of the cells have little "iron crosses" in them, not ticks. Copy the tick to all the cells, remove the custom formatting.

And, if you use the formula: =SUMIF(E2:E24, "ü",$F$2:$F$24), you can copy it left to the other columns.

**irnwil** · 08-09-2012, 04:11 PM

Thanks to all for the support! I have updated the custom format on E25 to be "General" as it should be, and used Tridom's solution, and it seems to work now! As a side note, the custom format was designed to make it very easy for an end user to enter any key stroke and get a checkmark. It was precisely this that I got hung up on. I should not have used "ü" but rather "<>" in the SUMIF statement in order to capture any value even though it shows as a WingDing checkmark symbol.

**tridom** · 08-09-2012, 06:02 PM

Originally Posted by irnwil

Thanks to all for the support! I have updated the custom format on E25 to be "General" as it should be, and used Tridom's solution, and it seems to work now! As a side note, the custom format was designed to make it very easy for an end user to enter any key stroke and get a checkmark. It was precisely this that I got hung up on. I should not have used "ü" but rather "<>" in the SUMIF statement in order to capture any value even though it shows as a WingDing checkmark symbol.

You are quite welcome. Sometimes the simplest of solutions works.

ChemistB and TMShucks are quite right about the formatting also. And the only reason I caught it WAS because you posted the spreadsheet. I, myself, learned something here too. I wasn't even paying attention to the formatting because I never used the symbols (or wingdings). I didn't know HOW you got the checkmarks in there....I just knew something was there. (:->) I'll need to file that info for future reference and use.

Glad to be of assistance.

**Cutter** · 08-09-2012, 06:11 PM

@ irnwil

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

**irnwil** · 08-09-2012, 11:53 PM

Thanks for those pointers. I will read up on the rules so I don't miss them in the future. I definetly thanked those who helped me.

How to sum only numbers in column B that have a corresponding "ü"/Checkmark in Column A

LinkBack

Thread Tools

Rate This Thread

Display

How to sum only numbers in column B that have a corresponding "ü"/Checkmark in Column A

Re: How to sum only numbers in column B that have a corresponding "ü"/Checkmark in Column

Re: How to sum only numbers in column B that have a corresponding "ü"/Checkmark in Column

Re: How to sum only numbers in column B that have a corresponding "ü"/Checkmark in Column

Re: How to sum only numbers in column B that have a corresponding "ü"/Checkmark in Column

Re: How to sum only numbers in column B that have a corresponding "ü"/Checkmark in Column

Re: How to sum only numbers in column B that have a corresponding "ü"/Checkmark in Column

Re: How to sum only numbers in column B that have a corresponding "ü"/Checkmark in Column

Re: How to sum only numbers in column B that have a corresponding "ü"/Checkmark in Column

Re: How to sum only numbers in column B that have a corresponding "ü"/Checkmark in Column

Re: How to sum only numbers in column B that have a corresponding "ü"/Checkmark in Column

Re: How to sum only numbers in column B that have a corresponding "ü"/Checkmark in Column

Re: How to sum only numbers in column B that have a corresponding "ü"/Checkmark in Column

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions