help with counting text in multiple columns?

krasp

New Member
Joined
Oct 3, 2006
Messages
9
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...

Thanks in advance!! :D
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

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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"
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi again

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

krasp

New Member
Joined
Oct 3, 2006
Messages
9

ADVERTISEMENT

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.
 

krasp

New Member
Joined
Oct 3, 2006
Messages
9
PGC, Yes I changed the DIAMOND to "DIAMOND" and it return a 0. instead of a 1.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

Easier if related to cells you have...

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

krasp

New Member
Joined
Oct 3, 2006
Messages
9
Aladin,
This formula works, Can you exlain to me how it works? Or, how to replicate it? THANK YOU!!!

Chris
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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
 

krasp

New Member
Joined
Oct 3, 2006
Messages
9
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
 

Forum statistics

Threads
1,136,366
Messages
5,675,351
Members
419,563
Latest member
blairtab

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top