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:

[TABLE="width: 676"]
<TBODY>[TR]
[TD]Data</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]OPTION</SPAN>[/TD]
[TD="colspan: 2"]OPTION</SPAN>[/TD]
[TD="colspan: 2"]OPTION</SPAN>[/TD]
[TD="colspan: 2"]OPTION</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A</SPAN>[/TD]
[TD]B</SPAN>[/TD]
[TD]A</SPAN>[/TD]
[TD]B</SPAN>[/TD]
[TD]A</SPAN>[/TD]
[TD]B</SPAN>[/TD]
[TD]A</SPAN>[/TD]
[TD]B</SPAN>[/TD]
[/TR]
[TR]
[TD]John</SPAN>[/TD]
[TD]1</SPAN>[/TD]
[TD]1</SPAN>[/TD]
[TD]1</SPAN>[/TD]
[TD]1</SPAN>[/TD]
[TD]1</SPAN>[/TD]
[TD]1</SPAN>[/TD]
[TD]1</SPAN>[/TD]
[TD]1</SPAN>[/TD]
[/TR]
[TR]
[TD]James</SPAN>[/TD]
[TD]2</SPAN>[/TD]
[TD]2</SPAN>[/TD]
[TD]2</SPAN>[/TD]
[TD]2</SPAN>[/TD]
[TD]2</SPAN>[/TD]
[TD]2</SPAN>[/TD]
[TD]2</SPAN>[/TD]
[TD]2</SPAN>[/TD]
[/TR]
[TR]
[TD]Ian</SPAN>[/TD]
[TD]3</SPAN>[/TD]
[TD]3</SPAN>[/TD]
[TD]3</SPAN>[/TD]
[TD]3</SPAN>[/TD]
[TD]3</SPAN>[/TD]
[TD]3</SPAN>[/TD]
[TD]3</SPAN>[/TD]
[TD]3</SPAN>[/TD]
[/TR]
[TR]
[TD]Sue</SPAN>[/TD]
[TD]4</SPAN>[/TD]
[TD]4</SPAN>[/TD]
[TD]4</SPAN>[/TD]
[TD]4</SPAN>[/TD]
[TD]4</SPAN>[/TD]
[TD]4</SPAN>[/TD]
[TD]4</SPAN>[/TD]
[TD]4</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL span=8></COLGROUP>[/TABLE]


Desired result (without using a pivot table)

[TABLE="width: 175"]
<TBODY>[TR]
[TD] [/TD]
[TD]Total Bs</SPAN>[/TD]
[/TR]
[TR]
[TD]John</SPAN>[/TD]
[TD="align: right"]4</SPAN>[/TD]
[/TR]
[TR]
[TD]James</SPAN>[/TD]
[TD="align: right"]8</SPAN>[/TD]
[/TR]
[TR]
[TD]Ian</SPAN>[/TD]
[TD="align: right"]12</SPAN>[/TD]
[/TR]
[TR]
[TD]Sue</SPAN>[/TD]
[TD="align: right"]16</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL></COLGROUP>[/TABLE]


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.