[Solved] SUMIF search criteria

[bobbert]

I'm trying to use the SUMIF function, but am unable to make it work the way i'd like. Instead of specifying the entire value for the search criteria, i'd like to be able to select records based on the first X number of characters in this field. For example, if the first 10 characters were always ACCOUNTING, but the rest of the field varied, is there a way to select records based only on the first 10 characters of the field? Hope i explained myself correctly.

[FJCC]

Try a formula like

Code: Select all

=SUMIF(D2:D7;"^accounting.*";E2:E7)

Be sure that regular expressions are enabled in the menu Tools -> Options -> OpenOffice Calc -> Calculate

[Lupp]

You may be even more flexible using SUMPRODUCT(). Assume column A is containing words and column B contains associated amounts.
If D2 now conatins a common keyword to find at the beginning of the words in column B for including the associated amount in the sum, you may use

Code: Select all

=SUMPRODUCT(B2:B101;LEFT(A2:A101;LEN(D2))=D2)

or similar.

[bobbert]

FJCC this works exactly the way i wanted it to. Thank you, thank you, thank you.