Countif?

[admack]

Hi, wondering if someone could help - or at least tell me if something is possible.

I am trying to Return the total number of B's from a range of data, I.e. if the cell above is a B I want it to count the number within the range. Something like this:

Data</SPAN>
	OPTION</SPAN>	OPTION</SPAN>	OPTION</SPAN>	OPTION</SPAN>
	A</SPAN>	B</SPAN>	A</SPAN>	B</SPAN>	A</SPAN>	B</SPAN>	A</SPAN>	B</SPAN>
John</SPAN>	1</SPAN>	1</SPAN>	1</SPAN>	1</SPAN>	1</SPAN>	1</SPAN>	1</SPAN>	1</SPAN>
James</SPAN>	2</SPAN>	2</SPAN>	2</SPAN>	2</SPAN>	2</SPAN>	2</SPAN>	2</SPAN>	2</SPAN>
Ian</SPAN>	3</SPAN>	3</SPAN>	3</SPAN>	3</SPAN>	3</SPAN>	3</SPAN>	3</SPAN>	3</SPAN>
Sue</SPAN>	4</SPAN>	4</SPAN>	4</SPAN>	4</SPAN>	4</SPAN>	4</SPAN>	4</SPAN>	4</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL span=8></COLGROUP>

Desired result (without using a pivot table)

	Total Bs</SPAN>
John</SPAN>	4</SPAN>
James</SPAN>	8</SPAN>
Ian</SPAN>	12</SPAN>
Sue</SPAN>	16</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL></COLGROUP>

I have tried using a COUNTIF but it aint working... any thoughts?

 

[AliGW]

Try SUMIF instead.

 

[JackDanIce]

For James, try:

=SUMPRODUCT(--($A$2:$H$2="B"),$A3:$H3)

 

[Rick Rothstein]

It is a little hard to tell what cells your values are in from your posting, so here are my assumptions. The first set of names is in cells A2:A5, the A/B headers are in Row 1, the actual data is in cells B2:I5, second set of names is in cells A10:A13 and the formula below should go in cell B10 and be copied down to cell B13.

=SUMPRODUCT((A10=A$2:A$5)*(B$1:I$1="B")*B$2:I$5)

 

[DigDoug]

Subscribed. I think I have a similar but hopefully less complicated question. If I need to start a new thread I will later but just wanted to see if this was an easy fix for someone on here. I am trying to return a value based on the value of two cells B1 and B2. No specific value, just any value(>1 I guess) and then return a 0, 1 or 2 in cell B3. If Cells B1 & 2 are blank then B3 should = 0. If cell B1 has a value and B2 is blank, then B3 should = 1. If both cells B1 & 2 have a value entered then B3 should =3. I am going to try and attach an image to hopefully better explain what I am after. Thank you in advance for your help and assistan

ce.

 

[Rick Rothstein]

Subscribed. I think I have a similar but hopefully less complicated question. If I need to start a new thread I will later but just wanted to see if this was an easy fix for someone on here. I am trying to return a value based on the value of two cells B1 and B2. No specific value, just any value(>1 I guess) and then return a 0, 1 or 2 in cell B3. If Cells B1 & 2 are blank then B3 should = 0. If cell B1 has a value and B2 is blank, then B3 should = 1. If both cells B1 & 2 have a value entered then B3 should =3. I am going to try and attach an image to hopefully better explain what I am after. Thank you in advance for your help and assistan

ce.

This is a completely different question than the this thread was started for, so you should start a new thread of your own for it. When you do, you will need to clarify two things...

1) You said "...and then return a 0, 1 or 2 in...", but then later said "If both cells B1 & 2 have a value entered then B3 should =3"... should that =3 have been =2 or was your 0,1,2 list wrong?

2) What should the formula return if B1 is blank and B2 has a value (you did not cover that one in your description of what you wanted the formula to return)?

 

[DigDoug]

Ok. Will start new thread. And yes I made an error it should be 2.