I am stumped. It seems like I have tried ever feasible combination with no joy. I dug into a few other options such as DSUM and was reading into array and pivot, but I am too far from being knowlegeable enough to go there.
I am trying to Count (COUNTIF) and Sum (SUMIFS) Column 'A' when Column 'B' is either "X" or "Y" or "Z" and Column 'C' is not "000" or "030" or "087" . . . . or "877" (a total of 45 exceptions) and Column 'D' is "1"
If I use an OR("X","Y","Z") it returns True or False and I get zero.
If I use ("X"+"Y"+"Z"), I get zero.
I have tried multiple formats with the exception list also with no joy. ("<>000","<>030", "<>087")
Here is the formula before the exception list is introduced:
=SUM(SUMIFS($A:$A, $B:$B,"X", $D:$D, "1"),SUMIFS($A:$A, $B:$B,"Y", $D:$D, "1"), SUMIFS($A:$A, $B:$B,"Z", $D:$D, "1"))
When I tried to use SUMIFS($A:$A, $B:$B, "X", $B:$B "Y", $B:$B, "Z", $D:$D, "1") it returned zero records.
The closest I have come listed each of the criteria in Column B (X, Y, Z) with the entire exception list of 45 items ("000", "030", "877" etc) and failed because it exceeded 255 characters. I even tried to splice it together using &""& or &","& but SUMIFS was not real happy.
I have also tried using a table array and it didnt come out right.
I must accept the data I am working with as text because the data in column 'C' can be alphanumeric.
Thanks for your time, in advance.
Bookmarks