Sumif - cyrrilic letter values only

[dbx]

Hello everybody,

I have a list of multiple client names mixed latin letters and cyrillic letters. In the first column I have the client name, in the second columns I have the amounts.
I'm trying to calculate the sum of amounts only for cyrillic named clients.

Any good idea is highly appreciated.

Client​	Amount​	Comment​
Клиент 1	100	Cyrillic name
Клиент 2	150	Cyrillic name
Клиент 3	200	Cyrillic name
Client 4	100	Latin Name
Client 5	300	Latin Name
Total	???

 

[ExcelToDAX]

There is a ChatGPT for Excel add-in that you can utilize. Assuming your table is called Table1 and you have the ChatGPT for Excel Add-In, you can use this formula:

=SUM(IFERROR(VALUE(AI.EXTRACT(Table1[#All],"Sum Amount column where Client values have cyrillic letters")),0))

 

[LegendaryAxe]

Try this:

Book2
	A	B	C	D
1	Client	Amount	Comment
2	Клиент 1	100	Cyrillic name	TRUE
3	Клиент 2	150	Cyrillic name	TRUE
4	Клиент 3	200	Cyrillic name	TRUE
5	Client 4	100	Latin Name	FALSE
6	Client 5	300	Latin Name	FALSE
7
8	Total	450
Sheet1

Cell Formulas
Range	Formula
D2:D6	D2	=IF(SUMPRODUCT(--(UNICODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))>=1024))>0,TRUE,FALSE)
B8	B8	=SUMIFS(B2:B6,D2:D6,TRUE)

 

[Fluff]

What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[dbx]

There is a ChatGPT for Excel add-in that you can utilize. Assuming your table is called Table1 and you have the ChatGPT for Excel Add-In, you can use this formula:

=SUM(IFERROR(VALUE(AI.EXTRACT(Table1[#All],"Sum Amount column where Client values have cyrillic letters")),0))

View attachment 121121

Thank you for your reply.
Unfortunately I don't have such Excel Add-in.

 

[dbx]

Try this:

Book2
	A	B	C	D
1	Client	Amount	Comment
2	Клиент 1	100	Cyrillic name	TRUE
3	Клиент 2	150	Cyrillic name	TRUE
4	Клиент 3	200	Cyrillic name	TRUE
5	Client 4	100	Latin Name	FALSE
6	Client 5	300	Latin Name	FALSE
7
8	Total	450
Sheet1

Cell Formulas
Range	Formula
D2:D6	D2	=IF(SUMPRODUCT(--(UNICODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))>=1024))>0,TRUE,FALSE)
B8	B8	=SUMIFS(B2:B6,D2:D6,TRUE)

Wow, many thanks LegendaryAxe!

Works perfect. Could you figure out how to avoid using column D checks? In my case I'm working with 20K+ rows and regulary adding more rows to the sheet.

Appreciate your support.

BR,
Nick

 

[dbx]

What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Hello Fluff,

Apologies, I'm new here
I'm using Office 365 (Apps for business), Windows 10.

 

[Fluff]

Thanks for that.

 

[Fluff]

How about

Fluff.xlsm
	A	B	C
1	Client	Amount	Comment
2	Клиент 1	100	Cyrillic name
3	Клиент 2	150	Cyrillic name
4	Клиент 3	200	Cyrillic name
5	Client 4	100	Latin Name
6	Client 5	300	Latin Name
7
8	Total	450
Data

Cell Formulas
Range	Formula
B8	B8	=SUM(MAP(A2:A6,B2:B6,LAMBDA(a,b,SIGN(SUM(--(UNICODE(MID(a,SEQUENCE(LEN(a)),1))>1024)))*b)))

 

[dbx]

=SUM(MAP(A2:A6,B2:B6,LAMBDA(a,b,SIGN(SUM(--(UNICODE(MID(a,SEQUENCE(LEN(a)),1))>1024)))*b)))

Amazing solution, Fluff!
Works perfect!
Many thanks for your effort - I'm going to study your formula.