sum if function

[Vikkib]

I have a cell (H12) with this data in it:
WP:WK52:3 :BEV01, the WK number changes each week. I need to add up the
amount BEV01 earns throughout the contract, the costs are in another column.
I have used this formula: -
=SUM(IF($H$5:$H$2475,Right(H12,"5"),$M$5:$M$2475)
Do I need to put in an IF function to specify BEV01 with the RIGHT function?

 

[Mike H]

Hi,

I'd do it like this

=SUMPRODUCT((RIGHT(H5:H2475,5)="BEV01")*(M5:M2475))

Mike

 

[Jacob Skaria]

Try the below *** array formula ***

=SUM(IF(RIGHT($H$5:$H$2475,5)="BEV01",$M$5:$M$2475))

If the length of the data in column H is always in the same format with 3
":" but ID not always 5 characters it is better to use the below formula

=SUM(IF(TRIM(RIGHT(SUBSTITUTE($H$5:$H$2475,":",REPT(CHAR(32),50)),50))="BEV01",$M$5:$M$2475))

If this post helps click Yes

 

[Dave Peterson]

If you're just checking for BEV01 as the final 5 characters:

=sumif($h$5:$H$2475,"*bev01",$m$5:$m$2475)
or
=sumif($h$5:$H$2475,"*"&"bev01",$m$5:$m$2475)
or
=sumif($h$5:$H$2475,"*"&right(h12,5),$m$5:$m$2475)
(if x99 held the long WP:WK52:3 :BEV01 string.)

=Sumif() is one of those nice functions that will use wildcards (the asterisk
represents any set of characters).