Pivot tables - using IF or COUNTIF in a Calculated field?

  • Thread starter Thread starter shubha.bala
  • Start date Start date
S

shubha.bala

Hi everyone,

I have created a pivot table which is looking good so far, however I
am having problems with this one additional column, which I think I
need a Calculated field for.

It looks like this:

Items A Items B
# Items Weighted # # Items Weighted
#
Red
Green
Blue
Purple

I also have a column in my source data called Priority. I want to
have a column beside Weighted # (for each of my Items) which shows me
the total number of rows in each colour group which has the priority
1. I don't care at all about the other priorities. I tried making a
calculated field that does this: =IF(Priority=1, 1, 0) but it isn't
working properly. It is showing me a value of 1 on some rows, but 0
on most of them.

I've tried it with other priority values and it still shows me
sporadic 1s. I also tried returning 1 for true and false, and it
showed a 1 in every column although I was expecting it to basically
show me a count of every single row with a value in Priority, since
I'm told calculated fields sum!

Lastly..I can't do this as a column item I think because it then ends
up showing me # Items and Weighted # based on priorities...but those
values I just want a total sum (priority doesn't matter).

I feel like I'm barking up the wrong tree with the IF idea. COUNTIF
gives me an error. Any thoughts?
 
Add a column to the source data, e.g. Priority1, and use a formula to
calculate if the priority value is 1:

=IF(E2=1,1,0)

Add the new column to the pivot table, as Sum of Priority1
 
Add a column to the source data, e.g. Priority1, and use a formula to
calculate if the priority value is 1:

=IF(E2=1,1,0)

Add the new column to the pivot table, as Sum of Priority1














--
Debra Dalgleish
Contextureshttp://www.contextures.com/tiptech.html- Hide quoted text -

- Show quoted text -
Click to expand...

That's what I was afraid of...so no way to do it without a new column?
 
I'm also still curious as to *what* the IF in the calculated field is
doing...it's clearly doing something because 3 of the rows give me
some value of 1, and it's doing something weird because of how it
shows me a 1 in every row when I return 1 for true and false.
 
I'm also still curious as to *what* the IF in the calculated field is
doing...it's clearly doing something because 3 of the rows give me
some value of 1, and it's doing something weird because of how it
shows me a 1 in every row when I return 1 for true and false.





- Show quoted text -
Click to expand...

Oh I figured it out...
I tried using a really cool equation which would return 1 if x = 1,
and 0 otherwise = round(sin(pi * 2^x-2)),0)
and through that eventually figured out that it is summing priority
FIRST, and THEN applying the equation. This is the most ridiculous
thing ever (for my purpose). But that explains the IF...it is only
returning 1 if the SUM of all priority equals 1, which is only true in
a couple of cases.

Is there any way around this summing in advance thing?
 
Back
Top