COUNTIF statement

[Lukums]

Hello,

Looking to do a COUNTIF statement I believe.

We are looking to find a particular salesperson in this instance Caitlin which is located in column B and then looking for STATE in column E if this is TRUE then total the values found in column H against all the lines found.

The formula will always be kept on Sheet "Total" in Cell "A4"

Example below:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Sales Person[/TD]
[TD]Quote Number[/TD]
[TD]Cust name[/TD]
[TD]State[/TD]
[TD]Post code[/TD]
[TD]Shed Size[/TD]
[TD]Quote Value[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Caitlin[/TD]
[TD][/TD]
[TD][/TD]
[TD]NSW[/TD]
[TD][/TD]
[TD][/TD]
[TD]$1000[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Caitlin[/TD]
[TD][/TD]
[TD][/TD]
[TD]QLD[/TD]
[TD][/TD]
[TD][/TD]
[TD]$9999[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Caitlin[/TD]
[TD][/TD]
[TD][/TD]
[TD]NSW[/TD]
[TD][/TD]
[TD][/TD]
[TD]$2000[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Caitlin[/TD]
[TD][/TD]
[TD][/TD]
[TD]NSW[/TD]
[TD][/TD]
[TD][/TD]
[TD]$3000[/TD]
[/TR]
</tbody>[/TABLE]

Probably an easy one for you guru's appreciate the help

Luke

 

[Peter_SSs]

Something like this?

Excel 2016
	A	B	C	D	E	F	G	H
1	Date	Sales Person	Quote Number	Cust name	State	Post code	Shed Size	Quote Value
2	1	Caitlin			NSW			$1,000
3	1	Caitlin			QLD			$9,999
4	1	Caitlin			NSW			$2,000
5	1	Caitlin			NSW			$3,000
Data

Excel 2016
	A	B
1	Sales Person	Caitlin
2	State	NSW
3
4	Total	6000
Total

Cell Formulas
Range	Formula
B4	=SUMIFS(Data!H2:H10,Data!B2:B10,Data!L1,Data!E2:E10,Data!L2)

 

[Lukums]

Something like this?

Excel 2016

	A	B	C	D	E	F	G	H
Sales Person	Quote Number	Cust name	State	Post code	Shed Size
Caitlin	NSW
Caitlin	QLD
Caitlin	NSW
Caitlin	NSW

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]Date[/TD]

[TD="align: right"]Quote Value[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]$1,000 [/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]$9,999 [/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]$2,000 [/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]$3,000 [/TD]

</tbody>

Data​

Excel 2016

	A	B
Sales Person	Caitlin
State	NSW
Total

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]6000[/TD]

</tbody>

Total​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B4[/TH]
[TD="align: left"]=SUMIFS(Data!H2:H10,Data!B2:B10,Data!L1,Data!E2:E10,Data!L2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Thanks for helping! unfortunately returning a result with 0.

 

[Peter_SSs]

Thanks for helping! unfortunately returning a result with 0.

Are the values in column H actual numbers, or text?
What happens if you put this in a blank cell on the data sheet? =ISNUMBER(H2)