Help with summing values based on multiple criteria, including first few characters of text in a cell

[Kriesinator]

I need to sum values from a range based on multiple criteria. First, are the values positive or negative. I have a cell that has "Positive" and "Negative" as a data filter. Then I have a data table similar to this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Cat[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Cats[/TD]
[TD]-1[/TD]
[/TR]
[TR]
[TD]Cat[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Cats and Cat[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Dog[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Cat and Dog[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Cat and Mouse[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Mouse[/TD]
[TD]-6[/TD]
[/TR]
</tbody>[/TABLE]

I need to be able to sum for positive or negative values for each "primary" row label, that is, the first whole word (or preferably fixed number of characters). So, when looking for positive "Dog", I want to return "4", from row 5, not "6", from the sum of row 5 and 6.

When I sum for "Cat", I want to return "10", excluding the "2" value from row 4.

I thought I could use some version of SUMIFS with one criteria as the positive value and the other as "LEFT(A1:A8,4)="Cat " to only include rows that start with "Cat_", but my syntax is not working or the "LEFT" command won't work with a range.

Any ideas on how to do this?

 

[FDibbins]

This will do what you want - to a point....
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr]
[tr][td]

1​

[/td][td]Cat[/td][td]3[/td][td]Positive[/td][td]

12​

[/td][/tr]

[tr][td]

2​

[/td][td]Cats[/td][td]-1[/td][td]Cat[/td][td][/td][/tr]

[tr][td]

3​

[/td][td]Cat[/td][td]4[/td][td][/td][td][/td][/tr]

[tr][td]

4​

[/td][td]Cats and Cat[/td][td]2[/td][td][/td][td][/td][/tr]

[tr][td]

5​

[/td][td]Dog[/td][td]4[/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td]Cat and Dog[/td][td]2[/td][td][/td][td][/td][/tr]

[tr][td]

7​

[/td][td]Cat and Mouse[/td][td]1[/td][td][/td][td][/td][/tr]

[tr][td]

8​

[/td][td]Mouse[/td][td]-6[/td][td][/td][td][/td][/tr]
[/table]
D1=SUMIFS($B$1:$B$8,$A$1:$A$8,$C$2&"*",$B$1:$B$8,IF($C$1="Positive",">0","<0"))

However, with only 3 letters to work on, the results are not as accurate as if there were more letters to work with

 

[Kriesinator]

That is my exact problem. I need to limit the criteria to the three or four leftmost letters and spaces and not find a match later in the cell in order to get an accurate response. I want to have the sum of each primary label (column A) on separate lines elsewhere in the spreadsheet. There are 16 total positive values that need to be subtotaled by Cat, Cats, Dog and Mouse and return 10, 2, 4 and 0 respectively. Then for the negative values, 0, -1, 0 and -6 respectively. Must be possible...

 

[AhoyNC]

Maybe something like this?
This is an array formula and must be entered with CTRL-SHIFT-ENTER (command-return on MAC).

Excel Workbook
	A	B	C	D	E
1	Cat	3	*	10	Cat
2	Cats	-1	*	4	Dog
3	Cat	4	*	*	*
4	Cats and Cat	2	*	*	*
5	Dog	4	*	*	*
6	Cat and Dog	2	*	*	*
7	Cat and Mouse	1	*	*	*
8	Mouse	-6	*	*	*
Sheet

 

[Kriesinator]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Cat[/TD]
[TD]3[/TD]
[TD]West[/TD]
[TD]0[/TD]
[TD]Cat North[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Cats[/TD]
[TD]-1[/TD]
[TD]North[/TD]
[TD]1[/TD]
[TD]Cat South[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Cat[/TD]
[TD]4[/TD]
[TD]West[/TD]
[TD]2[/TD]
[TD]Cat East[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Cats and Cat[/TD]
[TD]2[/TD]
[TD]East[/TD]
[TD]7[/TD]
[TD]Cat West[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Dog[/TD]
[TD]4[/TD]
[TD]East[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Cat and Dog[/TD]
[TD]2[/TD]
[TD]East
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Cat and Mouse[/TD]
[TD]1[/TD]
[TD]South[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Mouse[/TD]
[TD]-6[/TD]
[TD]South[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
That works great! Now I'll complicate a little further: In the table above, lets put another variable in column C. I'm now also trying to subtotal by the zone or region. I've got the answers I want in column D, but I don't understand the array formula enough to work in another variable. I can solve this with a pivot table, but I'm trying to avoid that because the data updates daily and I don't want to have to refresh the pivot table data.

I also mentioned that I have another variable, in another cell (lets say F1) that will toggle between "Positive" and "Negative" in order to add another function: Give me only positive value sum or negative value sum.

 

[AhoyNC]

Here are a couple ways to try.
The easy way would be to put the criteria into 2 different cell (Cat in E and North in column F). See 2nd example below.
The first example below uses your setup.
These are array formulas and must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B	C	D	E
1	Cat	3	West	0	Cat North
2	Cats	-1	North	1	Cat South
3	Cat	4	West	2	Cat East
4	Cats and Cat	2	East	7	Cat West
5	Dog	4	East	*	*
6	Cat and Dog	2	East	*	*
7	Cat and Mouse	1	South	*	*
8	Mouse	-6	South	*	*
Criteria One Cell

Excel Workbook
	A	B	C	D	E	F
1	Cat	3	West	0	Cat	North
2	Cats	-1	North	1	Cat	South
3	Cat	4	West	2	Cat	East
4	Cats and Cat	2	East	7	Cat	West
5	Dog	4	East	*	*	*
6	Cat and Dog	2	East	*	*	*
7	Cat and Mouse	1	South	*	*	*
8	Mouse	-6	South	*	*	*
Criteria in Two cells

 

[Kriesinator]

Success! I'm not quite sure what all this does, but it does it well.

 

[AhoyNC]

You're welcome. Thanks for the feedback.
Here is a quick summary of what the parts of the formula do:
This part

SEARCH(LEFT($E1,FIND(" ",$E1)-1)&" ",$A$1:$A$8&" ")
Returns this array {1;#VALUE!;1;10;#VALUE!;1;1;#VALUE!}

Since you want the first word in the string we are looking for the number 1. We also get an #VALUE error if it doesn't find the word so, we use IFERROR to turn those to 0.

IFERROR(SEARCH(LEFT($E1,FIND(" ",$E1)-1)&" ",$A$1:$A$8&" ")=1,0)
this returns {TRUE;0;TRUE;FALSE;0;TRUE;TRUE;0} which in this case are the rows starting with Cat.

This part finds the second word in cell E1 which is NORTH in this case.

[MID($E1,FIND(" ",$E1)+1,LEN(E$1)-FIND(" ",E$1)+1)/CODE]
So this just finds the rows with North in column C
[CODE][MID($E1,FIND(" ",$E1)+1,LEN(E$1)-FIND(" ",E$1)+1)=$C$1:$C$8
returns {FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}/CODE]
Hope that helps.