Counting values with IF value?
 

Hi,

I am struggling getting a formula to work and even quesitoning if I am
even using the right formula.

I want a total count my values (just number of) in column B based on IF
column A has a specific value ("L" or "D")

This formula almost works but only requires one "L" value to be present
in column A and I want it to look at each line through A2:A10

=COUNTA(IF(A2:A10="L",B2:B10))

Thanks!

-Wesley


--
wester69
------------------------------------------------------------------------
wester69's Profile: http://www.officehelp.in/member.php?userid=4689
View this thread: http://www.officehelp.in/showthread.php?t=1278723

Posted from - http://www.officehelp.in

Counting values with IF value?
 

Try this:

=SUMPRODUCT(--(ISNUMBER(MATCH(A2:A10,{"L","D"},0))),--(B2:B10<""))

Biff

"wester69" wrote in message
...

Hi,

I am struggling getting a formula to work and even quesitoning if I am
even using the right formula.

I want a total count my values (just number of) in column B based on IF
column A has a specific value ("L" or "D")

This formula almost works but only requires one "L" value to be present
in column A and I want it to look at each line through A2:A10

=COUNTA(IF(A2:A10="L",B2:B10))

Thanks!

-Wesley


--
wester69
------------------------------------------------------------------------
wester69's Profile: http://www.officehelp.in/member.php?userid=4689
View this thread: http://www.officehelp.in/showthread.php?t=1278723

Posted from - http://www.officehelp.in

Counting values with IF value?
 

I may not understand you correctly, but this will sum your values in column
B where Column A contains "L" or "D".

=SUMIF(A2:A10,"L",B2:B9)+SUMIF(A2:A10,"D",B2:B9)

HTH,
Paul

"wester69" wrote in message
...

Hi,

I am struggling getting a formula to work and even quesitoning if I am
even using the right formula.

I want a total count my values (just number of) in column B based on IF
column A has a specific value ("L" or "D")

This formula almost works but only requires one "L" value to be present
in column A and I want it to look at each line through A2:A10

=COUNTA(IF(A2:A10="L",B2:B10))

Thanks!

-Wesley


--
wester69
------------------------------------------------------------------------
wester69's Profile: http://www.officehelp.in/member.php?userid=4689
View this thread: http://www.officehelp.in/showthread.php?t=1278723

Posted from - http://www.officehelp.in

Counting values with IF value?
 

Try something like this:

With
A2:A10 containing any value or no value
B2:B10 containing any value or no value

This formula returns the count of the Col_B values (ignoring blanks) where
the corresponding Col_A value is either L or D:
C1: =SUMPRODUCT(((A2:A10="L")+(A2:A10="D"))*(B2:B10<" "))

This one only counts numeric values:
C1: =SUMPRODUCT(((A2:A10="L")+(A2:A10="D"))*ISNUMBER(B 2:B10))


Does that help?
***********
Regards,
Ron

XL2002, WinXP


"wester69" wrote:


Hi,

I am struggling getting a formula to work and even quesitoning if I am
even using the right formula.

I want a total count my values (just number of) in column B based on IF
column A has a specific value ("L" or "D")

This formula almost works but only requires one "L" value to be present
in column A and I want it to look at each line through A2:A10

=COUNTA(IF(A2:A10="L",B2:B10))

Thanks!

-Wesley


--
wester69
------------------------------------------------------------------------
wester69's Profile: http://www.officehelp.in/member.php?userid=4689
View this thread: http://www.officehelp.in/showthread.php?t=1278723

Posted from - http://www.officehelp.in

Counting values with IF value?
 

Shorter versions of my posted formulas:

Count any values
C1: =SUMPRODUCT((A2:A10={"L","D"})*(B2:B10<""))

Count numeric values
C1: =SUMPRODUCT((A2:A10={"L","D"})*ISNUMBER(B2:B10))


***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

Try something like this:

With
A2:A10 containing any value or no value
B2:B10 containing any value or no value

This formula returns the count of the Col_B values (ignoring blanks) where
the corresponding Col_A value is either L or D:
C1: =SUMPRODUCT(((A2:A10="L")+(A2:A10="D"))*(B2:B10<" "))

This one only counts numeric values:
C1: =SUMPRODUCT(((A2:A10="L")+(A2:A10="D"))*ISNUMBER(B 2:B10))


Does that help?
***********
Regards,
Ron

XL2002, WinXP


"wester69" wrote:


Hi,

I am struggling getting a formula to work and even quesitoning if I am
even using the right formula.

I want a total count my values (just number of) in column B based on IF
column A has a specific value ("L" or "D")

This formula almost works but only requires one "L" value to be present
in column A and I want it to look at each line through A2:A10

=COUNTA(IF(A2:A10="L",B2:B10))

Thanks!

-Wesley


--
wester69
------------------------------------------------------------------------
wester69's Profile: http://www.officehelp.in/member.php?userid=4689
View this thread: http://www.officehelp.in/showthread.php?t=1278723

Posted from - http://www.officehelp.in

Counting values with IF value?
 

Thanks Ron, your solution was the ticket . Thank you also to the others for
helping.

I was on a wrong path!

This is what I needed =SUMPRODUCT((A2:A10="L")*(B2:B10<"")). The other
solutions were adding only the numbered cells in my B column, were I needed
to just count them if they contained text, a number or cobination of text and
numbers.

Thanks Again!

-Wesley