Index Match with return a value "Yes" or "No"

[Gesyca_is_joy]

I have three tabs in a file, one is a list of projects and looks something like this:

Project Number	Project Name
1234	Sales Software Development
4567	Marketing materials
7894	Furniture Purchase
8829	Sales Suite Fit Out

on tab two I have an invoice log where i enter all the invoices against any project spend:

Invoice Number	Site	Amount	Project Code	Project Type
Invoice 1	HQ	$50,000	1234
Invoice 2	Michigan DC	$14,000	4567
Invoice 3	Washington State	$7,500	8829

I want to make a third tab which pulls total spend by project cluster and location but what I need it to is add a column called "Project Type" to the back end of my Invoice log, that looks at the project code in that table, finds it on the Project tab, then looks at the Project Name column for that Project and, if the project name has "Sales" in it, returns a value True False

Location	Total Spend
Michigan DC	Sum of all projects that have "Sales" in their Project Name

 

[etaf]

=COUNTIFS(Sheet1!A2:A5,Sheet2!D2,Sheet1!B2:B5,"*"&"sales"&"*")>0
will proved the true false

I want to make a third tab which pulls total spend by project cluster and location

whats a project cluster ?

the example
Michigan DC , does not have sales in the example

TAB1

Book3
	A	B
1	Project Number	Project Name
2	1234	Sales Software Development
3	4567	Marketing materials
4	7894	Furniture Purchase
5	8829	Sales Suite Fit Out
Sheet1

TAB2

Book3
	A	B	C	D	E
1	Invoice Number	Site	Amount	Project Code	Project Type
2	Invoice 1	HQ	$50,000	1234	TRUE
3	Invoice 2	Michigan DC	$14,000	4567	FALSE
4	Invoice 3	Washington State	$7,500	8829	TRUE
Sheet2

Cell Formulas
Range	Formula
E2:E4	E2	=COUNTIFS(Sheet1!A2:A5,Sheet2!D2,Sheet1!B2:B5,"*"&"sales"&"*")>0

SUMIFS() is probably the formula ??? but I'm not sure whats required on the 3rd TAB

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

 

[Gesyca_is_joy]

=COUNTIFS(Sheet1!A2:A5,Sheet2!D2,Sheet1!B2:B5,"*"&"sales"&"*")>0
will proved the true false


whats a project cluster ?

the example
Michigan DC , does not have sales in the example

TAB1

Book3
	A	B
1	Project Number	Project Name
2	1234	Sales Software Development
3	4567	Marketing materials
4	7894	Furniture Purchase
5	8829	Sales Suite Fit Out
Sheet1

TAB2

Book3
	A	B	C	D	E
1	Invoice Number	Site	Amount	Project Code	Project Type
2	Invoice 1	HQ	$50,000	1234	TRUE
3	Invoice 2	Michigan DC	$14,000	4567	FALSE
4	Invoice 3	Washington State	$7,500	8829	TRUE
Sheet2

Cell Formulas
Range	Formula
E2:E4	E2	=COUNTIFS(Sheet1!A2:A5,Sheet2!D2,Sheet1!B2:B5,"*"&"sales"&"*")>0

SUMIFS() is probably the same ??? but not sure

Apologies, a project cluster is a group of related projects, that have individual project codes but are all interconnected. So i would eventually need to be able to pull total spend for any given location for any given cluster of sales projects. but the clusters are text strings, such as "Sales" in the example.

 

[etaf]

still missing what you need really sorry

in your example you have 2 rows with sales in
Project - 1234
Project - 8829
so these are a CLUSTER
But the location is
HQ
and
Washington State

so non have
Michigan DC

Location	Total Spend
Michigan DC	Sum of all projects that have "Sales" in their Project Name

as shown in your example

I'm sorry i'm missing these

But i suspect a SUMIFS() is what you are looking for
just not sure how to construct the criteria

Or how to create the TAB 3

 

[Gesyca_is_joy]

Nope, the formula you put up was perfect, it worked like a charm. and returned a TRUE/FALSE that I then used on tab three as a criteria for Sum IFS

So it turned out I already had the formula mostly correct but the "*"&"sales"&"*" part of the equation was wrong, I hate doing stuff off text but that's all i have with the base file I get. I didn't type that part in correctly and so the formula failed.

 

[etaf]

glad its now working ok