SUMIFS with multiple criteria and partial text

Lizzi

Board Regular
Joined
Sep 23, 2011
Messages
146
Hi

looking for some help please, i'm trying to do a sumifs with multiple criteria and a partial text but i seen to be having issues

column A will contain "ABC and DEF" column L is the revenue amount and column F contains text "RNXXXX" (amongst other text) but i only want it to pick up the rev amount that corresponds to "ABC & DEF and "RN"

does that make sense?
column A Column F Column L Result would = 2,500
ABC RNXXX 1,000
DEF RNXXX 1,500
ABC POXXX 2,000

Many thanks with any help you can provide :)
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

svendiamond

Well-known Member
Joined
Jun 13, 2014
Messages
1,498
Office Version
  1. 365
Platform
  1. Windows
There is probably a fancier way using SUMPRODUCT but this should work for now:

=SUMIFS(L:L,A:A,"ABC",F:F,"RN*")+SUMIFS(L:L,A:A,"DEF",F:F,"RN*")
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,062
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,119,110
Messages
5,576,162
Members
412,702
Latest member
maggielrux
Top