Excel SUMIFS to Sum Project Values With or Without PPR Reference Text
Question details
The user needs to total project amounts for a selected project and date based on whether the reference text contains or does not contain the code PPR.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Creating a date-based project total that distinguishes references containing PPR from references that exclude PPR.
- Observed behavior
- Repeated IF, NOT, and SEARCH conditions can return an incorrect total when the project, date, and reference-text criteria are not applied consistently.
Identify the ranges for Amount, Project ID, Date, and Reference Text, and ensure they contain matching numbers of rows. Decide whether PPR should match any part of a reference, such as PPR1, or only an exact reference value.
Use two SUMIFS formulas for references that contain or exclude PPR
Use this method when the same project and date criteria must be applied to both PPR and non-PPR reference records.
SUMIFS can test text with wildcard criteria. Use "*PPR*" to include references containing PPR anywhere in the text, and "<>*PPR*" to include references that do not contain PPR.
Enter or select the target Project ID in a criteria cell such as B11 and the target date in a header or criteria cell such as C10.
In the total cell, enter a formula using your actual ranges: =SUMIFS(AmountRange,ProjectRange,$B11,DateRange,C$10,ReferenceRange,"*PPR*")+SUMIFS(AmountRange,ProjectRange,$B11,DateRange,C$10,ReferenceRange,"<>*PPR*"). Replace AmountRange, ProjectRange, DateRange, and ReferenceRange with the corresponding worksheet ranges.
Select Data > Filter, then filter the Project ID and Date columns to the same criteria. Review the Reference Text column to confirm that records with and without PPR are included as intended.
Use exact reference criteria when PPR must not match PPR1 or similar text
Use this option when only the exact reference value PPR belongs in the PPR total.
Sum PPR and Non-PPR Project Amounts With WPS Spreadsheets
WPS Spreadsheets supports SUMIFS and wildcard text criteria, so you can total project values by date while matching or excluding reference codes such as PPR. Paste your existing formula or build it with familiar spreadsheet functions and filters.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, select Spreadsheets, and open the Excel workbook containing the project data.
- 2. Select the destination total cell: Click the cell under the required date where the project total should appear.
- 3. Enter the SUMIFS formula: Type the two-SUMIFS formula, using "*PPR*" for references containing PPR and "<>*PPR*" for references excluding PPR.
- 4. Press Enter and verify the source rows: Press Enter, then choose Data > Filter to check the Project ID, Date, and Reference Text records included in the total.
Frequently Asked Questions
How do I sum values when a reference cell contains PPR in Excel?
Use SUMIFS with the reference criterion "*PPR*", such as =SUMIFS(AmountRange,ReferenceRange,"*PPR*"). Add your Project ID and Date range criteria in the same SUMIFS function.
How do I exclude references containing PPR from a SUMIFS total?
Use the criterion "<>*PPR*" for the Reference Text range. This returns rows whose reference text does not contain PPR, while the other SUMIFS criteria can still limit the result by project and date.
Why does my formula include PPR1 when I only want PPR?
The wildcard criterion "*PPR*" searches for PPR anywhere in the reference text, so it includes PPR1. Use "PPR" for an exact match, or use a more specific criterion if your reference format is consistent.
Can I use one SUMIFS formula for both PPR and non-PPR references?
Use two SUMIFS functions added together when you need separate include and exclude text criteria. Keeping the Project ID and Date criteria identical in both functions prevents mismatched totals.
Do blank reference cells match the <>*PPR* criterion?
Blank cells may be included because they do not contain PPR. If blanks must be excluded, add another criterion for the Reference Text range, such as "<>".
