# "IF" statement returning too many results

**Caleb Smith**Oct 17, 2013 2:49 PM

I'm trying to create a calculated field that returns a count/sum of the rows that meet its criteria. The reason I'm doing this with a calculated field rather than just using filters is because I need to use slightly different logic for each of 4 tables, later summing the results together.

Anyway, I started off with the following calculation:

SUM(IIF([STATUS] = "Staff Contingency Review"

OR [STATUS] = "Staff Review"

OR [STATUS] = "Reviewer Contingency Review"

OR [STATUS] = "Committee Decision"

OR [STATUS] = "Review Decision",

IF DATEDIFF('day',[RECEIPT_DT],TODAY()) > 7 AND DATEDIFF('day',[RECEIPT_DT],TODAY()) < 15 THEN 1 ELSE 0 END,0))

This returned 3,013 results. Now, If I just dropped the row IDs for this table directly onto the row shelf, I get around 127 rows total, nowhere near 3k. So I figured there is something wrong with my calculation. To troubleshoot it, I figured I'd build it up one layer at a time until I found the problem. So I did the following:

SUM(IF [STATUS] = "Staff Contingency Review" THEN 1 ELSE 0 END)

This gave me 12 results. There should only be 2 results. If I just do this with a filter, then I get the correct 2 results. I decided to graph the row ID and the calculation value so I could see which rows were being summed in order to figure out if there was some other value I should be considering. When I did this it looked like some IDs were being counted multiple times (some only one time, some 3 or 4). So then I wondered if it was the SUM that was messing things up.

COUNTD(IF [STATUS] = "Staff Contingency Review" THEN 1 ELSE 0 END)

This gave me 2 results. Bingo, I thought. Let's add another layer:

COUNTD(IF [STATUS] = "Staff Contingency Review" THEN 1 ELSE 0 END)+

COUNTD(IF [STATUS] = "Staff Review" THEN 1 ELSE 0 END)

This gave me 4 result. The correct number should be 7. OK, so COUNTD didn't work, let's take the aggregation out of things and just use IF statements, then making the pill a SUM pill rather than an AGG.

IF [STATUS] = "Staff Contingency Review" THEN 1 ELSE 0 END+

[STATUS] = "Staff Review" THEN 1 ELSE 0 END

This gave me 20-something results. The correct number should be 7. Again, some rows were being counted multiple times.

Now I've got myself tied in knots. I have no idea what I'm missing here. Why is the basic "SUM" giving me so many results? While if I simply drop the row ID onto the row shelf and then filter by status I get the correct numbers. I can't figure it out. Help!