Question on Countifs formula

[bmackie97]

I am trying to use the countifs function to count the number of times a vendor name appears in a range but also based on specific currencies. The issue is when there are multiple vendor names I need to count with the same currency. I can sum multiple countifs functions to get the answer I need but wondering if there is an easier and cleaner way to do this. I have included a brief sample below. Here I would want to count the number of times a vendor name is populated in column B if the currency is USD and The Vendor is A, B, or C.

Currency	Vendor
USD	A
USD	B
USD	C
USD	D
BRL	B
MXN	C

<colgroup><col style="width:48pt" span="2" width="64"> </colgroup><tbody>
</tbody>

 

[C Moore]

You could do this with Sumproduct, try this link Tricking Excel's SUMPRODUCT() - Ratfactor.

 

[adam087]

You could perhaps try something like this?


Excel 2010

	A	B
1	Currency	Vendor
2	USD	A
3	USD	B
4	USD	C
5	USD	D
6	BRL	B
7	MXN	C
8
9	USD	3

<colgroup><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula B9 {=SUM((A2:A7=A9)*(B2:B7={"A","B","C"}))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Hope that helps


/AJ

 

[JackDanIce]

Try:

=SUMPRODUCT(--($A$1:$A$8="USD"),--($B$1:$B$8="A"))

 

[bmackie97]

Thanks, I think this worked

 

[pgc01]

Hi
Welcome to the board

I am trying to use the countifs function ...

I don't understand why you'd want to use the CountIfs() function but, if that's the case, try:

=SUM(COUNTIFS(A2:A7,"USD",B2:B7,{"A","B","C"}))