sumprod when list contains & does not contain certain items of test

rservice0320 

If you want to sum values based on two conditions (whether a cell contains "ppr" or not) and then place the result in corresponding dates, you can use the SUMIFS function along with the SEARCH function for partial matching.

Assuming your data is structured like this:

• Column A: Date
• Column B: Project Reference

Here is an example formula to sum values for projects with "ppr" and without "ppr" for each date:

=SUMIFS($C$2:$C$100, $B$2:$B$100, "*ppr*", $A$2:$A$100, A2) + SUMIFS($C$2:$C$100, $B$2:$B$100, "<>*ppr*", $A$2:$A$100, A2)

Make sure to adjust the range $C$2:$C$100, $B$2:$B$100, and $A$2:$A$100 based on the actual range of your data.

Here's a breakdown of the formula:

• SUMIFS($C$2:$C$100, $B$2:$B$100, "*ppr*", $A$2:$A$100, A2): This part sums values where the Project Reference contains "ppr" and matches the date in column A.
• SUMIFS($C$2:$C$100, $B$2:$B$100, "<>*ppr*", $A$2:$A$100, A2): This part sums values where the Project Reference does not contain "ppr" and matches the date in column A.

By adding these two sums together, you get the total sum for each date considering both conditions.

Copy this formula down for each date in column A where you want to display the sums. Adjust the ranges according to your actual data. I don’t open your file for my own security reasons at the time.

The text was created with the help of AI.

If this not help you, I recommend adding more information to your topic. Information such as Excel version, operating system, storage medium, file extension, etc.

In this link you will find some more information about it:

Welcome to your Excel discussion space!

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.