Using COUNTA and COUNTIFS in the same formula

Hi

Can you post a sample worksheet so we can see the layout etc dont post confidentail information


To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.



Chris

as suggested, upload a sample file. but this may work for you...

=COUNTIFS(A:A,"2013",B:B, I9,E:E,"A",F:F,"B")

this assumes that your year data is just those 4 numbers, and not an actual date (12/14/2012), and also that "A" = that part A was taken, "B" = that part B was taken

try this, copied down and across

=COUNTIFS($A$2:$A$78,2013,$B$2:$B$78,$I9,$E2:$E78,"<>"&"")

im happy we helped.

i would just point out that you should try and restrict reference ranges if you can (i go 2-3 times more than what i think i will need). using entire rows or columns can become very resource-intensive, if there are many of them

Normally with the "IFS" functions such as SUMIF/SUMIFS, COUNTIF/COUNTIFS it's not an issue to use the whole column - Excel actually restricts the calculations to the "used range" so the efficiency shouldn't be compromised.

That's different with "array formulas" and SUMPRODUCT, though. There I would avoid full columns.

Note you can just use "<>" as the last criterion like

=COUNTIFS(A:A,2013,B:B,$I9,E:E,"<>")

thanks for the tips DDL