Forum Discussion
sumprod when list contains & does not contain certain items of test
Sorry folks that I am reposting my question, but I accidentally hit best reply. Though I appreciate the response, it didn't really solve the issue where I am looking to sum a Project when it has 2 conditions: with "ppr" in the reference column & without "ppr" in reference column & then place it correctly to the corresponding date.
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.
2 Replies
rservice0320 See attached! I continued where I left it in your other posting and completed the SUMIFS formula. Added the date criteria in the formula that checks if the it contains "ppr" and 1234. Then, the second formula can simply be a SUMIF that sums all projects 1234 and deducts the result from the first.
Now, if the other thread is not giving you the correct answer, best to just delete the entire thread. You should be able to do that, or add another post referring to this thread.
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.
