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

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

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
50,929
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,118,861
Messages
5,574,708
Members
412,613
Latest member
EFRATA
Top