Troubleshoot:How do/can you use multiple criteria in one criteria argument in SUMIFS & COUNTIFS functions? Error

How do/can you use multiple criteria in one criteria argument in SUMIFS & COUNTIFS functions?

How do/can you use multiple criteria in one criteria argument in SUMIFS & COUNTIFS functions?
For example, how do I get something like this to work?  The first values "Apple", "Front" & "On" will result as 1 but if you use a mixture like "Apple", "Back" & "On" you get a zero.  The formula below feels intuitive enough and I've tried an array version that
I can't get to work, either.
=COUNTIFS(B:B,"Apple","Pear","Orange",C:C,"Front","Back","Middle",D:D,"On")
Array version:
=COUNTIFS(B:B,E1:E3,C:C,F1:F3,D:D,"On")

Keys to the Problem How do/can you use multiple criteria in one criteria argument in SUMIFS & COUNTIFS functions?

Download Error Fixer (Free)

Biff,
Another question here.  Is there any operational difference, that you know of, between the formula you gave and then me replacing the double unary negative with the function N (as shown below)?
=SUMPRODUCT(N(ISNUMBER(MATCH(B2:B10,G2:G4,0))),N(ISNUMBER(MATCH(C2:C10,H2:H4,0))),N(D2:D10=I2))
Thanks,
aDNA

The double unary is slightly more efficient over larger ranges.
See this for more info on SUMPRODUCT and the use of the double unary:
http://xldynamic.com/source/xld.SUMPRODUCT.html
--
Biff
Microsoft Excel MVP

This particular error can easily be resolved with a few simple steps. However, if you are not confident in your ability to use the methods below or they do not fully remove the issue on your computer, you may want to seek the assistance of a qualified repair technician who is well versed in fixing How do/can you use multiple criteria in one criteria argument in SUMIFS & COUNTIFS functions?.

1. Back-up the Registry of your operating system.
2. Find the Registry Key: [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\OSUpgrade]
3. Note: this Registry Key should already exist, if it does not exist, create it.
4. Create a brand new DWORD Value that is named AllowOsUpdate
5. Set the Value to 0x00000001.
6. Close and reopen your Control Panel.
7. Restart the Microsoft Windows.

Recommended Method to Fix the Problem: How do/can you use multiple criteria in one criteria argument in SUMIFS & COUNTIFS functions?:

 

 

How to Fix How do/can you use multiple criteria in one criteria argument in SUMIFS & COUNTIFS functions? with SmartPCFixer?

1. Download Error Fixer. Install it on your computer.

2. After the scan is finished, you can see the errors and problems which need to be repaired.

3. The Fixing part is finished, the speed of your computer will be much higher than before and the errors have been removed.

Related: KMODE EXCEPTION NOT HANDLED with BSOD restarts OK everytime laptop will not shut down while plugged to power source shuts down properly from battery Minor loss of fidelity Some cells or styles in this workbook contain formatting that is not supported by the selected file format Microsoft not letting me copy and paste mero 9 nine essentials will not let me restore my computer at an earliar time time My computer keeps restarting when I shut it down and I can not access advanced start up via pressing F8 Microsoft Silverlight was installed and I cannot uninstall it MY PRODUCT KEY DOESNOT WORK My Windows Mail freezes when I get a notification from Facebook My Windows 7 does not detect HP 8500A Plus Printer microsoft bluetooth notebook mouse 5000 not staying connected new tab reads element not found in IE 8 microsoft word starter will not open file that was downloaded from email not a valid Win32 app error my dell laptop as a internle mic and it is not working,Can I backup different folders to different external harddisks using Windows Backup and how sony vaio drivers DNS Connection only connection to one DNS provider Windows 7 64bit vaio drivers Installing Adobe Flash Player and Adobe Reader for 64 bit Windows 7 and Explorer 9 Beta vaio drivers Process of upgrading Windows Mail to Windows Live Mail sony vaio drivers Startup asks to select Windows 7 or prior OS vaio drivers update Windows 7 Home Prem Windows Updates Sony VAIO Driver Download Windows7 and Itunes Installation Upgrade Issues HKEY errors vaio drivers update Realtek LAN Driver Version V7.088.0617.2014 , 6.04 MBytes
Read More: [Solved] External storage usage possible with Office Live?,Why and how do Windows get dragged or text get highlighted without clicking? Tech Support,Fast Solution to Error: My Microsoft Bluetooth Notebook Mouse 5000 doesn't work-,How Can I Fix - I am trying to link up my 1and1.co.uk email account to Windows Mail. Incoming mail is working but sending just won't work.?,How to Resolve - How to retain the formatting of row and column labels in pivot tables?,Why Are My Email Addresses Declared Invalid By Multiple Websites?,(any website) is not set up to establish a connection on port world wide web service (http) with this computer.,Xerox Docuprint P8ex stopped working,how to sent mail merge to two Recipient,my computer keeps shutting down??