countifs

**ria333** · 07-18-2012, 12:54 PM

I am trying to use a CountIFS statement to get a total of 1 column, but only if another column is a specific value:

So Column A is the column I am summing based on the value and Column Q needs to be a value of "1" to even be included:

When I do the following (only inserting the a Criteria, it works):

=(COUNTIF(A6:A347,"VL")*2)+(COUNTIF(A2:A347,"L")*10)+(COUNTIF(A2:A347,"ML")*40)+(COUNTIF(A2:A347,"M")*80)+(COUNTIF(A2:A347,"H")*150)+(COUNTIF(A2:A347,"VC")*460)

I thought I could do the following to add in the other column criteria (Q); but it doesn't work:

=(COUNTIFS(A6:A347,"VL",Q6:Q347 = "1")*2)+(COUNTIFS(A2:A347,"L",Q6:Q347 = "1")*10)+(COUNTIFS(A2:A347,"ML",Q6:Q347 = "1")*40)+(COUNTIFS(A2:A347,"M",Q6:Q347 = "1")*80)+(COUNTIFS(A2:A347,"H",Q6:Q347 = "1")*150)+(COUNTIFS(A2:A347,"VC",Q6:Q347 = "1")*460)

Please help - I want the original countif to work as it does (first line above) but only when colum Q = "1".

Thanks

**JBeaucaire** · 07-18-2012, 12:56 PM

You can get corrected syntax for your formulas by pressing F1 and reading the builtin examples for the functions.

COUNTIF uses pairs of parameters. You paired the first two, but then you made a "formula" out to of the next two.

COUNTIFS(Range1, Test1, Range2, Test2, Range3, Test3)

COUNTIFS(A6:A347, "VL", Q6:Q347, 1)

**Cutter** · 07-18-2012, 12:59 PM

Welcome to the forum.

Try

=(COUNTIFS(A6:A347,"VL",Q6:Q347,1)*2)+(COUNTIFS(A2:A347,"L",Q6:Q347,1)*10)+(COUNTIFS(A2:A347,"ML",Q6:Q347,1)*40)+(COUNTIFS(A2:A347,"M",Q6:Q347,1)*80)+(COUNTIFS(A2:A347,"H",Q6:Q347,1)*150)+(COUNTIFS(A2:A347,"VC",Q6:Q347,1)*460)

**ria333** · 07-18-2012, 01:45 PM

I had tried that, but tried it again and I get a VALUE error. It will work if I only add it to 1 of the multiple COUNTIFs functions, but not when I try to do more than 1.