Sum and Countif Combination Formula

1. you dont need to use {} for only 1 criteria ('Employee Data'!$G$19:$G$223,{"00"})
2, if these are real numbers, then remove the ""
{"6100","6525","6590"}
3. if you still have a problem, upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

can you upload a sample workbook?

I'll wait for the sample file, so we dont have to guess

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

Thats interesting. I cannot see why it is not working, but the last part (active/inactive) seems to be the problem. If those are the only 2 ??active criteria you have, this seems to work...
=SUM(COUNTIFS(C8:C22,{"01","05"},D8:D22,{"6100","6103"},E8:E22,"FT",F8:F22,"*active"))

I have asked some other experts for their input, I am probably overlooking something here

Hi.

To put it simply, you have "run out" of dimensions.

See here for an explanation on the limitations to using COUNTIFS with multiple OR criteria:

http://excelxor.com/2014/09/28/count...iteria_ranges/

You would need to switch to SUMPRODUCT here:

=SUMPRODUCT(0+ISNUMBER(MATCH(C8:C22,{"01","05"},0)),0+ISNUMBER(MATCH(D8:D22,{"6100","6103"},0)),0+ISNUMBER(MATCH(F8:F22,{"active","inactive"},0)),0+(E8:E22="ft"))

Regards

Formula:

Please Login or Register  to view this content.

this works but suggest you move onto sumproduct like xor mentions if your going to have that many criteria

Thanks for the input, guys, I had a feeling it was something like that

@humdingaling

That only works by pure coincidence in this particular case, but is logically flawed.

For example, change some of the "6100" entries which occur next to a "01" to "6103". The result should still be 8, though your formula will not give that result.

Perhaps you also need to read the post in the link I provided?

Regards