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

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"
 
Upvote 0
Hi again

Please notice that I edited the formulae and replaced DIAMOND by "DIAMOND"
 
Upvote 0
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.
 
Upvote 0
Aladin,
This formula works, Can you exlain to me how it works? Or, how to replicate it? THANK YOU!!!

Chris
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
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.
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
Back
Top