Combine two COUNTIF criteria using SUM not going well

[Padedo]

Here is my formula:

=SUM(Retirement1!$B$6:$CT$8="*"&$D61)*(Retirement1!$A$11:$CT$11="Complete")
I also make sure to enter using Shift Ctrl

The first criteria I am trying to pick up a piece of a cell(s) that has the particular related sell inside of it. The second criteria is a different line/cell(s) that the value is equal to the text "Complete"

Stand alone both criterias work by themselves, abd I have made this successful when both criterias are combined by just a fix cell (e.g. B1) and a fix text (e.g. "Complete" like above).

What am I doing Wrong?

[daddylonglegs]

The range sizes don't match, are you intentionally using 3 rows in Retirement1!$B$6:$CT$8? (and the columns are different from Retirement1!$A$11:$CT$11)

You can't use wildcards in that fashion, assuming two equally sized rows of data try this version with SUMPRODUCT

=SUMPRODUCT((RIGHT(Retirement1!$A$6:$CT$6,LEN($D61))=$D61)*(Retirement1!$A$11:$CT$11="Complete"))

[Padedo]

Thanks Daddy long legs. Great formula! I intentionally have selected multiple rows, but overlooked that the columns were not matching. I have used your formula matching only the columns and it works!

I actual took all day today and found out a much longer way to do this by creating multiple new tabs first etc., and I get the same result with the formula you produced so it checks out. If only I had realized your post sooner (did not expect a reply in under 10 mins!) Thanks!


Final Formula
=SUMPRODUCT((RIGHT(Retirement1!$A$6:$CT$8,LEN($D61))=$D61)*(Retirement1!$A$11:$CT$11="Complete"))