3 Replies Latest reply on Nov 24, 2018 6:32 PM by Chris McClellan

# Help with Sum and Count IF Calculation

HI,

I have two events (CIO & CEO) with invite responses (Accept, No Response, Regret, Cancel) over 3 years (2016, 2017, 2018).

This data looks like this in my Excel that is linked to Tableau...

Company Name     Event     Year     Response

Acme                      CIO        2015     Accept

BMORG                  CEO      2018     Decline

What I want to do is calculate how many times a company has accepted CIO event over the 3 years.

Likewise, I want to calculate how many times a company has accepted the CEO event over the 3 years.

But also I want to calculate how many times a company did not accept (Cancel, Decline, or No Response) CIO or CEO event over the 3 years.

I have revenue data associated with these accounts, and ultimately I'd like to see if those who frequently attend our events have stronger revenues with our company, or at least stronger than those companies who don't attend our event.

JC

• ###### 1. Re: Help with Sum and Count IF Calculation

So you have an ACME record for each year and a BMORG record for each year ?

I would do an LOD with a conditional count, so something like :

CIO accepts: {FIXED [Company Name] : SUM(if [Event] = "CIO" and [Response]="Accept" then 1 end) }

CEO accepts: {FIXED [Company Name] : SUM(if [Event] = "CEO" and [Response]="Accept" then 1 end) }

Not accepts: {FIXED [Company Name] : SUM(if [Response]="Accept" then 1 end) }

Let me know if it doesn't work as you require.

• ###### 2. Re: Help with Sum and Count IF Calculation

Hi Chris,

I agree with your first two calcs.

For the third one I think you meant to use "not equals" instead of "equals":

Not accepts:

{FIXED [Company Name] : SUM(if [Response] <> "Accept" then 1 end) }

Best,

Paul

• ###### 3. Re: Help with Sum and Count IF Calculation

You're right, just a copy & paste error