SOLVED

combine sum logic if a cell contains certain text & exclude adding if has certain text

Brass Contributor

I have certain projects with various reference #'s.  I would like to sum the projects that contain a specific identifier & then sum the same project that excludes that identifier.  I have attached a file.  I tried the "if(not(isnumber(search"  repeatedly, but obviously not doing something correctly.  Thank you!

6 Replies

@rservice0320 As I see it, your formula first looks for if A10 contains "ppr". If so, it sums the values from the where the ref# equals A10 (i.e. "PPR1") AND where the project ID equals B10 (i.e. 1234). And then some more conditions for looking at dates.

 

There is only one ref# PPR1 with project 1234 and it amounts to 7000, which is the result of the formula.

 

I've added a few formulas to your file and hope that you can use them to rebuild your own. If not, please com back here and clarify your intentions and the expected end result.

sorry, I accidently hit "best reply". I thank you for your formula, but what the other cell in the file should show is $5000 because what I want the formula to do is look at Project 1234 & further look to see if the ref# contains "ppr" or not & come up with different sums & then put it in the correct data category. So project 1234 with no "ppr" should equal $5000 & the one without "ppr" should equal $7000 & place it under 1/22/24.

@rservice0320 I merely pointed out a basic flaw in your formula as I was not sure what you were trying to achieve. Still don't really understand. Sorry. 

 

I removed the "Best reply" for you.

in my file, cell E11 should be equal to $5000 as what I want to do is sum project 1234 without a Ref# containing "ppr" as well as sum project 1234 with a ref# of "PPR" & then place it under the correct date. again...apologies for reposting the question...don't know how to remove the reposting
best response confirmed by rservice0320 (Brass Contributor)
Solution

@rservice0320 

Does the attached version return the intended result?

Oliver,
This is exactly what I needed! Thank you so much!
1 best response

Accepted Solutions
best response confirmed by rservice0320 (Brass Contributor)
Solution

@rservice0320 

Does the attached version return the intended result?

View solution in original post