sum column while excluding corresponding text values?

  • Thread starter Thread starter Dan-X !
  • Start date Start date
D

Dan-X !

I want to sum a column of numbers in another workbook but exclude
numbers from my total that have certain words ("batch" and "state") in
a corresponding column. So using the table example below my formula
would return a sum of 303.

Table example:

Routing Events
Flood Plain Mapping 101
Mapping by Batch 150
Manual Determination 101
State Reconsideration 199
Individual Reconsideration 101

Make sense?

Based on some other formulas I saw in newsgroups here is a formula I
tried to use but it didn't work.

=SUM((Queue_User_Routing.csv!$E$2:$E$2000)*MATCH((Queue_User_Routing.csv!$C$2:$C$2000<>"Batch")*(Queue_User_Routing.csv!$C$2:$C$2000<>"State")))

Any help would be appreciated.

Thanks,
-Danny
 
When I have a complex problem, I first look for a solution to part of the
problem. In this case I wanted to solve your problem but with the data on
one worksheet.
The worked for me.
=SUM(B1:B5)-(SUMIF(A1:A5,"*batch*",B1:B5)+SUMIF(A1:A5,"*state*",B1:B5))
I get 303 with your data
SUMPRODUCT is not the answer to every maiden's prayer!
I expect you will be able to adapt it.
best wishes
 
Thanks for the response. Naturally right after I posted I stumbled
upon a formula that worked. It may be bulky but it does work. (See
below.)

One issue I have with it though is that I had to include the entire
contents of the cells I wish to exclude. I couldn't come up with a
wildcard to let Excel know that I wanted to exclude all occurrences of
any cells that had the word "batch" or "state" in them, as you can see
from my formula below.

Now my next problem is that I want to reverse or flip this formula so
that the sum only ~includes~ occurrences of these words or exact
values. I thought all I would have to do is change <> to = but that
gave me an error message. I was surprised.

=SUMPRODUCT((Queue_User_Routing.csv!$C$2:$C$2000<>"Manual Flood Plain
Mapping by Batch")*(Queue_User_Routing.csv!$C$2:$C$2000<>"Post
Inspection Flood Plain Mapping by
Batch")*(Queue_User_Routing.csv!$C$2:$C$2000<>"Approval by
Batch")*(Queue_User_Routing.csv!$C$2:$C$2000<>"State Exception
Review")*(Queue_User_Routing.csv!$C$2:$C$2000<>"State
Reconsideration")*Queue_User_Routing.csv!$E$2:$E$2000)

Anyone know how to reverse this formula so that it only counts
occurrences with the stated text values? (Anyone know a text wildcard
so that it includes/excludes any occurrence of specific words, such as
"batch" and "state", so that I don't have to enter each possible
value in full?)

Thanks,
-Danny
 
You might as well do it by hand if you have code so many explicit
exceptions. Wild cards work with SUMIF
Look at my formula! The part =SUMIF(A1:A5,"*batch*",B1:B5) will sum all the
B-value when the corresponding A-values contain the word 'batch'.
best wishes
 
Dan-X ! said:
Thanks for the response. Naturally right after I posted I stumbled
upon a formula that worked. It may be bulky but it does work. (See
below.)

One issue I have with it though is that I had to include the entire
contents of the cells I wish to exclude. I couldn't come up with a
wildcard to let Excel know that I wanted to exclude all occurrences of
any cells that had the word "batch" or "state" in them, as you can see
from my formula below.

=SUMPRODUCT((NOT(ISNUMBER(SEARCH("Batch",Queue_User_Routing.csv!$C$2:$C$2000
)))*
(NOT(ISNUMBER(SEARCH("State")*Queue_User_Routing.csv!$C$2:$C$2000)))*
Queue_User_Routing.csv!$E$2:$E$2000)


Now my next problem is that I want to reverse or flip this formula so
that the sum only ~includes~ occurrences of these words or exact
values. I thought all I would have to do is change <> to = but that
gave me an error message. I was surprised.

=SUMPRODUCT((Queue_User_Routing.csv!$C$2:$C$2000<>"Manual Flood Plain
Mapping by Batch")*(Queue_User_Routing.csv!$C$2:$C$2000<>"Post
Inspection Flood Plain Mapping by
Batch")*(Queue_User_Routing.csv!$C$2:$C$2000<>"Approval by
Batch")*(Queue_User_Routing.csv!$C$2:$C$2000<>"State Exception
Review")*(Queue_User_Routing.csv!$C$2:$C$2000<>"State
Reconsideration")*Queue_User_Routing.csv!$E$2:$E$2000)

Anyone know how to reverse this formula so that it only counts
occurrences with the stated text values? (Anyone know a text wildcard
so that it includes/excludes any occurrence of specific words, such as
"batch" and "state", so that I don't have to enter each possible
value in full?)
Click to expand...


=SUMPRODUCT((ISNUMBER(SEARCH({"Batch","State"},Queue_User_Routing.csv!$C$2:$
C$2000)))*
Queue_User_Routing.csv!$E$2:$E$2000)
 
Thanks everyone!! That last formula suggestion worked out great!

One more question just out of curiosity. In another posting I asked
for help with counting values in correlation to a column of text. A
formula was suggested and it worked. However, that was at the office
and when I opened the spreadsheet at home I'm getting a #DIV/0 error.
At work I use Office XP but at home I have Office 2000. Could the
difference in these versions be the reason the formula doesn't work at
home?

Just in case anyone wants to see it, here's the formula I'm referring
to:

=SUMPRODUCT((Queue_User_Routing.csv!$B$2:$B$2000<>"")/COUNTIF(Queue_User_Routing.csv!$B$2:$B$2000,Queue_User_Routing.csv!$B$2:$B$2000&""))


Thanks again to everyone for your assistance and great suggestions.

-Danny
 
No, it can only be because the data is different and at home this part

COUNTIF(Queue_User_Routing.csv!$B$2:$B$2000,Queue_User_Routing.csv!$B$2:$B$2
000&""))

resolved to 0

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Back
Top