sumif

P

Philh3325

I am trying to sum the data in the Data table as Regions (see Results table),
using the Lookup table to find the region (Lookup Table), without the need to
have a lookup column (XX) to Sumif on in the Data Table. Can anyone help
please.
See below.

Lookup Table Data Table Results Table
NHU Region NHU Data XX Region Solution
a aa a 1 aa aa 63
b bb a 2 aa bb 57
c aa a 3 aa cc 51
d aa b 4 bb dd
e bb b 5 bb ee
f cc b 6 bb etc
c 7 aa
c 8 aa
c 9 aa
d 10 aa
d 11 aa
d 12 aa
e 13 bb
e 14 bb
e 15 bb
f 16 cc
f 17 cc
f 18 cc
 
B

Bob Phillips

=SUM((ISNUMBER(MATCH(C2:C19,IF(B2:B7=F2,A2:A7),0)))*(D2:D19))

as an array formula, Ctrl-Shift-Enter it
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

incremental counting based on criteria 4
average values and reduce and simplify the worksheet 2
how to extract information from one table to another one 2
Group/Sort records in Excel 1
Cell Counter/Increment 2
IF formulas 5
Having difficulties using COUNTIF with filtered columns 3
How can I put a reference to a cell into a formula? 2

Top