function SUMPRODUCT with LEFTor ALL

[vysa]

Hey, i have some problem.

C1 = A B C D and etc.
how properly write: C1 begins from any letters

=SUMPRODUCT(--(LEFT(Sheet2!$C$2:$C$100000;1)=$C$1);--(Sheet2!$Y$2:$Y$100000=$D20);Sheet2!$P$2:$P$100000)

[JBeaucaire]

The letters of the alphabet all fall in the "code" range of 65-122 in the font table. So you can use that...

=SUMPRODUCT(--(CODE(LEFT(Sheet2!$C$2:$C$100000))>=65); --(CODE(LEFT(Sheet2!$C$2:$C$100000))<=122); --(Sheet2!$Y$2:$Y$100000=D20); Sheet2!$P$2:$P$100000)

[vysa]

Perhaps there are any simpler way ? only change cell C1

[JBeaucaire]

If you want to match column C to the letter of the alphabet you place in C1, and that can be any letter, then your original formula seems to already do that.

[vysa]

but at the same time i would like to have and with all letters result.

[JBeaucaire]

Maybe I am missing something, my formula will return a result for all possible letters, your formula will return a result for any single letter. What else are you seeking?

[vysa]

Please take a look excel file.

the formula shows 0, when i set any filter *
Microsoft Excel Worksheet.xlsx

[popipipo]

Please Login or Register  to view this content.

Try this one

[vysa]

Yes, that one i know. But both criteria might be *, perhaps there is in excel any symbol which means no criteria.

[Jacc]

Try this:

Please Login or Register  to view this content.