# Question on Countifs formula

#### bmackie97

##### New Member
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>

### Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
You could perhaps try something like this?

Excel 2010
AB
1CurrencyVendor
2USDA
3USDB
4USDC
5USDD
6BRLB
7MXNC
8
9USD3

</tbody>
Sheet1

Array Formulas
CellFormula
B9{=SUM((A2:A7=A9)*(B2:B7={"A","B","C"}))}

</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

Try:
Code:
``=SUMPRODUCT(--(\$A\$1:\$A\$8="USD"),--(\$B\$1:\$B\$8="A"))``

Thanks, I think this worked

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

Replies
3
Views
317
Replies
1
Views
449
Replies
17
Views
1K
Replies
3
Views
127
Replies
1
Views
127

1,203,521
Messages
6,055,882
Members
444,830
Latest member

### 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