Problem with sumif & countif functions

[Jzfbkr]

Round	Date	Fixture	Team	Formation	Scored
1	9/13/2013	TEAM A v TEAM B	TEAM A	4-4-2	3
1	9/13/2013	TEAM A v TEAM B	TEAM B	4-4-2	0
2	10/01/2013	TEAM A v TEAM C	TEAM A	4-4-2	1
2	10/01/2013	TEAM A v TEAM C	TEAM C	3-5-2	1
3	10/08/2013	TEAM D v TEAM A	TEAM D	3-4-3	2
3	10/08/2013	TEAM D v TEAM A	TEAM A	4-4-2	1

<tbody>
</tbody>

Im having issues using the sumif/countifs functions within this data set. Im convinced the formula ive been using is correct but '0' just keeps appearing. Can anyone think of any reasons why this could be happening and how it can be sorted.

Above is the data set im using.
A4:F10 on my first sheet.

on the second sheet I have the conditions that I would like the data to be sorted.

TEAM NAME

<tbody>
</tbody>

Cell L18

FORMATION

<tbody>
</tbody>

Cell K24



I would like to Search Column C on the first sheet to see if it contains 'TEAM NAME' Cell on the second sheet. If it does I would then like to Search Column D for a cell that does not contain 'TEAM NAME' Cell.

Then match the formation in Cell K24 counting the cells in row F that match the criteria.

anyhelp with this would be much appreciated i've been struggling on this for a while now.

Thanks

 

[skywriter]

Round	Date	Fixture	Team	Formation	Scored
1	9/13/2013	TEAM A v TEAM B	TEAM A	4-4-2	3
1	9/13/2013	TEAM A v TEAM B	TEAM B	4-4-2	0
2	10/01/2013	TEAM A v TEAM C	TEAM A	4-4-2	1
2	10/01/2013	TEAM A v TEAM C	TEAM C	3-5-2	1
3	10/08/2013	TEAM D v TEAM A	TEAM D	3-4-3	2
3	10/08/2013	TEAM D v TEAM A	TEAM A	4-4-2	1

<tbody>
</tbody>

Im having issues using the sumif/countifs functions within this data set. Im convinced the formula ive been using is correct but '0' just keeps appearing. Can anyone think of any reasons why this could be happening and how it can be sorted.

Above is the data set im using.
A4:F10 on my first sheet.

on the second sheet I have the conditions that I would like the data to be sorted.

TEAM NAME

<tbody>
</tbody>

Cell L18

FORMATION

<tbody>
</tbody>

Cell K24



I would like to Search Column C on the first sheet to see if it contains 'TEAM NAME' Cell on the second sheet. If it does I would then like to Search Column D for a cell that does not contain 'TEAM NAME' Cell.

Then match the formation in Cell K24 counting the cells in row F that match the criteria.

anyhelp with this would be much appreciated i've been struggling on this for a while now.

Thanks

I find your description of what you want a little confusing. It would be more helpful if you would reference your data and give an actual example of what you want to search for and what the correct result would be.

 

[FormR]

Maybe:

=COUNTIFS(Sheet1!C:C,"*"&Sheet2!L18&"*",Sheet1!D:D,"<>"&Sheet2!L18,Sheet1!E:E,Sheet2!K24)

 

[Momentman]

Whats the formula you are currently using?

Might this be what you need?

Excel 2010
	A	B	C	D	E	F	G	H
1	Round	Date	Fixture	Team	Formation	Scored
2	1	9/13/2013	TEAM A v TEAM B	TEAM A	4-4-2	3
3	1	9/13/2013	TEAM A v TEAM B	TEAM B	4-4-2	0
4	2	10/01/2013	TEAM A v TEAM C	TEAM A	4-4-2	1
5	2	10/01/2013	TEAM A v TEAM C	TEAM C	3-5-2	1
6	3	10/08/2013	TEAM D v TEAM A	TEAM D	3-4-3	2
7	3	10/08/2013	TEAM D v TEAM A	TEAM A	4-4-2	1		4
Sheet1

Cell Formulas
Range	Formula
H7	=COUNTIFS($C$2:$C$7,"*"&Sheet2!L18&"*",Sheet1!$E$2:$E$7,Sheet2!K24)

 

[Jzfbkr]

I'd like to SUM the goals scored based on the above criteria:

If Column C contains TEAM A
Column D equals TEAM A
Column E equals the given formation.

The sumif function I am currently using keeps returning 0

could it be something to do with cell formating or macros I am running?

 

[FormR]

Edit: missed the sum part.

 

[FormR]

Edit: missed the sum part.

Try:
=SUMIFS(Sheet1!F:F,C:C,"*"&Sheet2!L18&"*",Sheet1!D:D,Sheet2!L18,Sheet1!E:E,Sheet2!K23)

 

[Jzfbkr]

I've managed to fix the problem thanks for your help guys!

Massively Appreciated