# Thread: COUNTIF statement Thanks: 0 Likes: 0

1. ## COUNTIF statement

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:

 Date Sales Person Quote Number Cust name State Post code Shed Size Quote Value 1 Caitlin NSW \$1000 1 Caitlin QLD \$9999 1 Caitlin NSW \$2000 1 Caitlin NSW \$3000

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

Luke

2. ## Re: COUNTIF statement

Something like this?

Excel 2016
ABCDEFGH
1DateSales PersonQuote NumberCust nameStatePost codeShed SizeQuote Value
21CaitlinNSW\$1,000
31CaitlinQLD\$9,999
41CaitlinNSW\$2,000
51CaitlinNSW\$3,000

Data

Excel 2016
AB
1Sales PersonCaitlin
2StateNSW
3
4Total6000

Total

Worksheet Formulas
CellFormula
B4=SUMIFS(Data!H2:H10,Data!B2:B10,Data!L1,Data!E2:E10,Data!L2)

3. ## Re: COUNTIF statement

Originally Posted by 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

Worksheet Formulas
Cell Formula
B4 =SUMIFS(Data!H2:H10,Data!B2:B10,Data!L1,Data!E2:E10,Data!L2)
Thanks for helping! unfortunately returning a result with 0.

4. ## Re: COUNTIF statement

Originally Posted by Lukums
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)