7 Replies Latest reply on Apr 24, 2018 5:34 PM by Keith Braithwaite

Sumifs

I would like to create a calculated field where I count records based on multiple criteria. In excel the formula is =countifs(AccountID,[@AccountID],CreatedDate,>[@CreatedDate]). Is this possible? I included a sample table below. Any help would be appreciated

AccountIDCreated DateUserContacts per Account (Desired Result)Contacts per Account (Calculated Field)
joe@mail.com1/1/2018

Agent

3
sue@mail.com1/1/2018Agent 21
joe@mail.com1/5/2018Agent 22
sue@mail.com1/6/2018Agent0
joe@mail.com1/8/2018Agent1
pete@mail.com1/7/2018Agent 20
joe@mail.com1/12/2018Agent 20
• 1. Re: Sumifs

can you post a fake data set or workbook and some results.

• 2. Re: Sumifs

Hi, Keith

Yes, something like below in calculation field will work

sum(

if [AccountID] = 'Joe@mail.com' and [CreatedDate]> Date('1/1/2018')

)

ZZ

• 3. Re: Sumifs

Here is a sample workbook.

• 4. Re: Sumifs

Thanks for the reply Zhouyi for the answer. My one concern is that this is a fairly large data set. Is it possible that there is a solution that doesn't have a static email address?

• 5. Re: Sumifs

Hi, Keith

You can use wild match like below

sum(

if contains([AccountID,'@mail.com']) and [CreatedDate]> Date('1/1/2018')

)

ZZ

• 6. Re: Sumifs

Keith,

Can you explain with your test data what are we trying to get here so that I may help.

Thanks

Deepak

• 7. Re: Sumifs

What I would like to do is count how many times an account called after initial contact. The only problem is that there are around 1 million contacts and the countif function is crashing excel so I’m trying to find an alternative solution. Attached is a small sample with the countif function working properly.

Any help would be greatly appreciated. Thanks.