Two formulas in one cell calling by table name

[Outdoorsman80]

I posted a thread awhile ago on two formulas in one cell and that worked great, but I've found that I like calling tabled data and am trying to do something a little different.

I have a table "tblItems" that has a ton of data in it. I have a formula that looks for a keyword and year to get me a total of that item in one year

Data.xlsx
	A	B
16	Broilers Total	17
OO Dash

Cell Formulas
Range	Formula
B16	B16	=+SUM(SUMIFS(tblItems[Amount],tblItems[Description], {"Broiler"}, tblItems[Date],">="&DATE(2021,1,1), tblItems[Date], "<="&DATE(2021,12,31), tblItems[In Place], {"Yes","No"}))

Inside of B16 cell, where it says 17, like for it to actually say something like this based on another calculation:
17 (x Died)

Now, in the formula, under "In Place", there's a Yes No and Died. I've tried a few different variations but Excel keeps "Spilling" my formula and am not sure why.

 

[jasonb75]

Inside of B16 cell, where it says 17, like for it to actually say something like this based on another calculation:
17 (x Died)

To do something like that you would need to use a formula on the basis of =SUMIFS(....)&" ("&SUMIFS(...,"died")&" died") where the second sumifs counts the number that died.

Without the source table to see what we are looking at, there is not enough information for us to write the full formula correctly.

 

[Outdoorsman80]

The source would be In Place, Yes, No, Died.

Here's a sample of the source (tblItems):

Source.xlsx
	F	G	H	I
42	Date	Description	Amount	In Place
43	4/29/2021	Broiler	1	Yes
44	4/29/2020	Broiler	1	No
45	4/29/2021	Broiler	1	Died
Sheet1

 

[Outdoorsman80]

Did I stump everyone?

 

[jasonb75]

I thought that I posted this earlier but it didn't submit correctly for some reason.

I'm assuming that based on those 3 rows, the result should be "2 (1 died)"?

=SUM(SUMIFS(tblItems[Amount],tblItems[Description], "Broiler", tblItems[Date],">="&DATE(2021,1,1), tblItems[Date], "<="&DATE(2021,12,31), tblItems[In Place], {"Yes","No"}))&" ("&SUMIFS(tblItems[Amount],tblItems[Description], "Broiler", tblItems[Date],">="&DATE(2021,1,1), tblItems[Date], "<="&DATE(2021,12,31), tblItems[In Place], "Died")&" died)"

 

[Outdoorsman80]

That did it, thank you!

 

[Outdoorsman80]

I'm trying to figure this one out on my own here but it's just not working. In the same cell, I wanted to have it calculate this out:
21 (4 died, 17 processed)

=SUM(SUMIFS(tblItems[Amount],tblItems[Description], "Broiler", tblItems[Date],">="&DATE(2021,1,1), tblItems[Date], "<="&DATE(2021,12,31)))
&" ("&SUMIFS(tblItems[Amount],tblItems[Description], "Broiler", tblItems[Date],">="&DATE(2021,1,1), tblItems[Date], "<="&DATE(2021,12,31), tblItems[In Place], "Died")&" died, )"
&" ("&SUMIFS(tblItems[Amount],tblItems[Description], {"Broiler"}, tblItems[Date],">="&DATE(2021,1,1), tblItems[Date], "<="&DATE(2021,12,31), tblItems[In Place], {"Yes","No"}) &" Processed")"

It will count all purchased in a year, then, the ones that died in that year, then simple math or formula to subtract the two numbers to get the total processed. What am I doing wrong?

 

[jasonb75]

I haven't set up a test sheet, at a quick glance it looks like you're missing SUM() on the last part. You don't need it on the first part as there is no array.

=SUMIFS(tblItems[Amount],tblItems[Description], "Broiler", tblItems[Date],">="&DATE(2021,1,1), tblItems[Date], "<="&DATE(2021,12,31))
&" ("&SUMIFS(tblItems[Amount],tblItems[Description], "Broiler", tblItems[Date],">="&DATE(2021,1,1), tblItems[Date], "<="&DATE(2021,12,31), tblItems[In Place], "Died")&" died, "
&SUM(SUMIFS(tblItems[Amount],tblItems[Description], {"Broiler"}, tblItems[Date],">="&DATE(2021,1,1), tblItems[Date], "<="&DATE(2021,12,31), tblItems[In Place], {"Yes","No"})) &" Processed")"

I took out the closing bracket in " died, )" and removed the " ("& part at the start of the next line as they would have given you "21 (4 died, )(17 Processed)"

 

[Outdoorsman80]

I got this; tried tweaking it some but I'm still trying to grasp these advanced equations.

 

[jasonb75]

There's a mistake right at the end that I missed, one to many double quotes.

=SUMIFS(tblItems[Amount],tblItems[Description], "Broiler", tblItems[Date],">="&DATE(2021,1,1), tblItems[Date], "<="&DATE(2021,12,31))
&" ("&SUMIFS(tblItems[Amount],tblItems[Description], "Broiler", tblItems[Date],">="&DATE(2021,1,1), tblItems[Date], "<="&DATE(2021,12,31), tblItems[In Place], "Died")&" died, "
&SUM(SUMIFS(tblItems[Amount],tblItems[Description], {"Broiler"}, tblItems[Date],">="&DATE(2021,1,1), tblItems[Date], "<="&DATE(2021,12,31), tblItems[In Place], {"Yes","No"})) &" Processed)"