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.

<colgroup><col style="width:48pt" span="2" width="64"> </colgroup><tbody>
[TD="class: xl62, width: 64"]Currency[/TD]
[TD="class: xl62, width: 64"]Vendor[/TD]

[TD="class: xl62"]USD[/TD]
[TD="class: xl62"]A
[/TD]

[TD="class: xl62"]USD[/TD]
[TD="class: xl62"]B[/TD]

[TD="class: xl62"]USD[/TD]
[TD="class: xl62"]C[/TD]

[TD="class: xl62"]USD
[/TD]
[TD="class: xl62"]D
[/TD]

[TD="class: xl62"]BRL[/TD]
[TD="class: xl62"]B[/TD]

[TD="class: xl62"]MXN[/TD]
[TD="class: xl62"]C[/TD]

</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
Currency	Vendor
USD	A
USD	B
USD	C
USD	D
BRL	B
MXN	C
USD

<colgroup><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]3[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B9[/TH]
[TD="align: left"]{=SUM((A2:A7=A9)*(B2:B7={"A","B","C"}))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



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"}))