Countifs Not returning the same value for all cell

TheoKhalid

New Member
Joined
Dec 30, 2019
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
I have the table below and I want to find out how many times each item in column F, "Manufacturer Part #" is used on each machine. The "Machine #" is listed in column G. I am using the "countifs" statement;

"=COUNTIFS(F:F,F3,G:G,"5201 - Electrical")"

but it gives me a 1 even for the rows that have "5202 - Electrical" in column containing the machine #.

My understanding is that when you select the entire Column, excel checks if criteria is met in rows corresponding to the cell in which calculation is being done.


Example; if the above formula is used in column J3, then the execution will be as follows:

Check entire column F if it contains the value in Cell F3, if true then,

Check entire column G if it contains the value "5201 - Electrical", if true then count goes up by 1.



Issue: why is it returning a 1 for machines that are not "5201 - Electrical".

Any and all help is appreciated. Thank you.


1577736551101.png
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Your understanding of COUNTIFS is very flawed ... the results are as expected, following the inputs you have entered.

Column J is counting where F:F is, for example, the same as F3, AND also has a corresponding entry in column G of "5201 - Electrical" ... that is 1 on every row because there is one instance of 100-C09ZJ01 with "5201 - Electrical" in the list, and the same for every other combination.

In fact, I can't work out what it is that you are trying to count, from your description.
 
Upvote 0
I want to count how many times each "Manufacturer Part #" in column F occurs on Machine # "5201 - Electrical".
 
Upvote 0
I want to count how many times each "Manufacturer Part #" in column F occurs on Machine # "5201 - Electrical".

If it is the same "Manufacturer Part #" that occurs on a machine that is not "5201 - Electrical" then I want it to return 0.
 
Upvote 0
I think this might be what you want:

Formula in H2:
=COUNTIFS($F$2:F2,F2,$G$2:G2,"5201 - Electrical")*(G2="5201 - Electrical")
Copy down.

Book1
FGH
1Manufacturer Part #Machine #5201 - Electrical
2100-C09ZJ015201 - Electrical1
3100-C09ZJ015202 - Electrical0
4100-C09ZJ015201 - Electrical2
5100-C09ZJ015202 - Electrical0
Sheet1
Cell Formulas
RangeFormula
H2:H5H2=COUNTIFS($F$2:F2,F2,$G$2:G2,"5201 - Electrical")*(G2="5201 - Electrical")
 
Upvote 0
Glenn is correct. You appear to misundestand how to use COUNTIFS.

When you use Ranges like F:F and G:G in COUNTIFS, you are checking the ENTIRE column at once, not a single row!
There is exactly one row in that range meeting that criteria.
If you copy the formula down many times, you are just probably repeating the same exact formula many times.

If you want to check each row separately, you would need to change those column references to row indinvidual cell references, i.e. F2:F2 and G2:G2.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

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