Sum specific word in area

szakharov7723

Board Regular
Joined
Jun 22, 2018
Messages
66
Hello,
I have 5 columns, all of them can contain specific word
6th column is numbers.
Is it possible to take all 5 columns, find if cells contain specific word and sum the number if it does.

For example here I want to sum numbers of red only :
redblueyellow5
blueredyellowblue2
yellowbluered4
greengreenbluered8

<tbody>
</tbody>

the result will be 5+2+4+8 =19

I tried Sumproduct (--(A1:E4="red"),F1:F4) And it didn't work.
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,305
=SUMPRODUCT((A1:E4="red")*(F1:F4))

gives 19

NOTE: ALL occurrences of the colour add the row number,

so Blue gives 21
Row 1, 1 occurrence = 5
Row 2, 2 occurrences = 2 + 2
Row 3, 1 occurrence = 4
Row 4, 1 occurrence = 8
= 5 + 2 + 2 + 4 + 8 = 21
 
Last edited:

Forum statistics

Threads
1,078,151
Messages
5,338,531
Members
399,240
Latest member
Anell JvR

Some videos you may like

This Week's Hot Topics

Top