Hi guys,
I'm a little upset about one "simply" function. I have data in one row. In every cell is value(number) but it begins with a letter(character). I need to sum VALUE ot these cells based on the character. Could anyone help me with this one? sum.jpg
In you pic, the sums are switched, right? the sum of the C's is 10 and the sum of D's is 13. Or do I misunderstand what you want?
When I say semicolon, u say comma!
Yup the sums are swapped, sorry for that. Anyway do you have a clue?
Hi,
As an array formula entered with Ctrl Shift Enter
Formula:
Please Login or Register to view this content.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Are your actual data rows also just 5 columns long? If so, I can write you a formula. If not, I think you need an array, which I know too little about.
Well, I would like to use it for attendance record. So there will be used maximum 31 columns.
Haha there's your array
Originally Posted by Richard Buttrey
Hi,
As an array formula entered with Ctrl Shift Enter
Formula:Please Login or Register to view this content.
Thanks guys, It works perfectly!
Great! Please mark the thread as solved (above the first post) and reward Richard with a Reputation Point (lower left corner of his post).
Here's a non-array formula.
A B C D E F 1 Data D8 D4 C2 D1 C8 2 3 Totals C 10 4 D 13
This formula entered in C3 and copied down:
=SUMPRODUCT((LEFT(B$1:F$1)=B3)*RIGHT(B$1:F$1))
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Hi, I have one more problem with two-digit numbers in the data cells.
The result is counted correctly, but it displays only the last digit of the number. How can I set the result as a two-digit number?
In the picture you can see that both results for character "C" are 6. But the corect result is 16. two-digit.png
Try this updated version of Tony's formula
=SUMPRODUCT((LEFT(B$1:F$1)=B3)*RIGHT(B$1:F$1,LEN($B$1:$F$1)-1))
Life's a spreadsheet, Excel!
Say thanks, Click *
Here's another one...
Data Range
This formula entered in C3 and copied down:
=SUMPRODUCT((LEFT(B$1:F$1)=B3)*MID(B$1:F$1,2,15))
Thanks guys for your help,
but now it works only with two-digit values and not with single ones/
When I enter value for example "8", both formulas make an error: #VALUE!
It is possible to make a formula that works both singledigit and two-digit numbers? singledigit.png
Thank you so so much
Try this...
Data Range
This array formula** entered in C3 and copied down:
=SUM(IF(ISTEXT(B$1:F$1),IF(LEFT(B$1:F$1)=B3,--MID(B$1:F$1,2,15))))
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Thank you Tony, now it works perfectly. You are awesome!
You're welcome. Thanks for the feedback!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks