Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

Formula help: Sum of cells with multiple entries

Copper Contributor

I believe it is the sumifs formula that I will use, but I don't know if there is a better way or how to go best about it. I am looking to add the total of cells under incentive amount but based on email, the issue is that there are multiple rows that have the same email and some that are individual emails. I need to find the people that have incentives over $600 excluding any cell row that says "parking". I put a picture below of an example of what the data looks like. I don't know if it would be better to auto populate the sum of the incentives based by name/email in another chart as well, and if that is better I would like to know the formula on how to do that too. Thanks!

 

Miyana_1-1704487596906.png

 

 

2 Replies
best response confirmed by Miyana (Copper Contributor)
Solution

@Miyana 

You can use

 

=SUMIFS(incentive_range, email_range, email, type_range, "<>Parking")

 

You might also enter the following formula in a cell elsewhere:

 

=UNIQUE(email_range)

 

and in the cell next to it

 

=SUMIFS(incentive_range, email_range, unique_email_cell#, type_range, "<>Parking")

 

The # after the unique_email_cell specifies the spill range of the UNIQUE formula.

Thank you so much!! You helped me a lot today.
1 best response

Accepted Solutions
best response confirmed by Miyana (Copper Contributor)
Solution

@Miyana 

You can use

 

=SUMIFS(incentive_range, email_range, email, type_range, "<>Parking")

 

You might also enter the following formula in a cell elsewhere:

 

=UNIQUE(email_range)

 

and in the cell next to it

 

=SUMIFS(incentive_range, email_range, unique_email_cell#, type_range, "<>Parking")

 

The # after the unique_email_cell specifies the spill range of the UNIQUE formula.

View solution in original post