EXCEL SUMIF & FIND

[Franois_91]

Hi,

I have Windows 10 and Microsoft 365 license on a laptop.  I have a large Excel table with headers in the first column.

For each column, I would like to add the numbers in all lines where a given word appears in the first column headers

I know how to use SUMIF with a criterion which is an exact match of the header but not if the word appears in the header (headers are short phrases). 

I suspect I need to combine SUMIF and FIND but do not know how to do it.

Can someone help me, please ?

Thanks

François

[#2]

@Franois_91 

Sorry, I didn't catch - you would like to sum numbers for select based on some criteria header; or you would like to add something to each value in such column?

[#3]

Hello Sergei,

I have "years" in column headers and "clients" in line headers.  The table is filled with numbers (of contracts).

I would to like to know how many contracts I had for each "year column" for a selected criterion such as a single client word; this is because the line headers include the client name (single word) but also other words.

I hope this is clearer.

thanks

[#4 · Accepted solution]

@Franois_91 

That could be something like this

as array formula if you are on "old" Excel. 

=SUM( INDEX( $C$3:$J$4, MATCH( 1, ISNUMBER( SEARCH(B7, $B$3:$B$4) )*1, 0 ), 0 ) )

 If on 365 or 2021 that could be done bit other way.

[#5]

thank you Serguei !

[#6]

@Franois_91 , you are welcome. Please note, I didn't test on Excel without dynamic arrays.

[#7]

@Sergei Baklan 

Sergei,

I ended up using the following formula:

=sumifs(C$3:C$4;$B$3":$B$4;"*"&"client"&"*")

Thanks for your help.

François