# help with counting text in multiple columns?

#### krasp

##### New Member
Hello!! I am a newbie so I have to get out the usual thank the lord for the board and its users, etc. So...

I have text in clumns A and B but only want to count certain entries in A based on the entry in B.. Detailed question after the html code...

Book1.xls
ABCDEF
1AGENTORDERSOURCECOPLETEDSOURCETOTALDAVE
2MIKEDIAMONDYESDIAMOND3#VALUE!
3DAVEDIAMONDYESCLAY2
4JIMMILLERNOMILLER4
5MIKECLAYYESTOTALS9
6MIKEDIAMONDYES
7JIMMILLERNOCOMPLETED6
8JIMMILLERYESNOTCOMPLETE3
9JIMCLAYNO
10APRILMILLERYES
needtextcountingformulas

I need a formula to count how many times DAVE received an order from DIAMOND.
I assumed something like =COUNT(B:B,"DIAMOND")*IF(A:A,DAVE) but that doesn’t work
ALSO
I need a formula to count how many times DAVE received an order from DIAMOND that were 'completed'.

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Hi krasp
Welcome to the board

For the first question try

=SUMPRODUCT(--(A2:A10="DAVE"),--(B2:B10="DIAMOND"))

For the second:

=SUMPRODUCT(--(A2:A10="DAVE"),--(B2:B10="DIAMOND"),--(C2:C10="YES"))

Hope this helps
PGC

EDIT: Replaced DIAMOND by "DIAMOND"

...and yes, this simple task is aching my brain! lol :D

Hi again

Please notice that I edited the formulae and replaced DIAMOND by "DIAMOND"

PGC, Thanks for the quick reply! I have tried the SUMPRODUCT formula as well as the COUNT*IF but I keep getting a #NAME? error with all my attempts.

PGC, Yes I changed the DIAMOND to "DIAMOND" and it return a 0. instead of a 1.

Easier if related to cells you have...

=SUMPRODUCT(--(\$A\$2:\$A\$10=F\$1),--(\$B\$2:\$B\$10=\$D2))

This formula works, Can you exlain to me how it works? Or, how to replicate it? THANK YOU!!!

Chris

Hi again Krasp

Aladin's formulation is, no doubt, better.

I was just trying to show you the mechanics of counting with multiple criteria.

I just tried and the formula worked fine for me. Could it be that you have, for ex., spaces after the names?

Kind regards
PGC

pgc, I don't have sapces after the entries. Thank you for showing me an alternative counting method!! :D

Aladin, Can you use that same formula to also use coulmn C as criteria (only count A:A if MIKE, if B:B is DIAMOND and C:C is YES

Replies
3
Views
175
Replies
1
Views
170
Replies
3
Views
273
Replies
4
Views
222
Replies
5
Views
210

1,218,513
Messages
6,142,907
Members
450,453
Latest member
spaudel

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back