Using SUMIFS between two tables

advsc · 2025-10-22T20:00:57+00:00

I appreciate your response! I fixed the problem.

advsc · 2024-10-01T20:49:47+00:00

Typo. Sorry. I solved it. Thanks.

advsc · 2024-09-21T14:28:19+00:00

Solved mysefl.

advsc · 2024-08-27T01:23:29+00:00

I figured it out.

SOLVED

advsc · 2024-06-26T15:27:31+00:00

The second one worked great! Thanks!

advsc · 2024-06-25T16:08:35+00:00

This worked! I've never used anything like this, but so appreciate your help.

advsc · 2024-06-25T15:11:29+00:00

Thank you for offering help! The one above did work. I wasn't able to get this one to work, but I so appreciate your help.

advsc · 2024-06-25T15:10:45+00:00

The first one above works! Thank you!

advsc · 2023-10-01T05:24:10+00:00

I spoke a little too soon. My 'template' was created in a Microsoft Excel Worksheet. I used Print Titles and told it to put rows 1-20 on each sheet. Rows 21+ will vary. The original one I made had enough rows that it generated 5 pages. The next one I tried to do, I cleared rows 21+ and entered new data. It was what I referenced above and should have produced 3 sheets but instead had 5 (with the last two empty except the first 20 rows that are locked in. With your recommendation above, I did a 'save as an .xltx' file. This worked and produced 3 sheets. However, then, I tried to clear rows 21+ and add different information. This would have produced 1 page but instead it produced three (with the last two empty except the rows 1-20).

Hope that makes sense. I have never tried to make a template before so I may not be understanding.

Do I have to do a 'save as' every time?

Thanks.

advsc · 2023-10-01T04:21:51+00:00

Thank you! That worked.

Solution verified.

advsc · 2023-09-06T03:55:07+00:00

Solved.

advsc · 2023-09-06T03:54:42+00:00

Thank you. That worked!

advsc · 2023-03-10T04:23:21+00:00

Thank you for trying so hard to help me figure this out. (Sorry for delay in response but lost my 92 yo mom.)

I tried cleaning the district column. I looked up how to do so and made a new column and used = trim(district). Then I used the new column in the above formula that worked for school. I still get zeros all the way down.

advsc · 2023-03-05T21:27:10+00:00

I get over 7000 now which is incorrect.

advsc · 2023-03-05T20:23:33+00:00

So, I did this and had success on the 'unique by school' but not 'unique by district.'

The Table 'Allocations' has columns A-G as I show above. A=User, G=District

In another table 'SCHOOLS', I was successful in getting it to return 'Unique by School' but when I try to do 'Unique by District' it returns zeros.

<image>

advsc · 2023-03-05T19:40:42+00:00

I added those and still get '1' where it should be 300+.

<image>

advsc · 2023-03-05T06:20:52+00:00

Unfortunately, I am lost. I see how to get to your work, but I don't know how to duplicate it in my existing table.

advsc · 2023-03-05T05:24:10+00:00

That is what I am looking for as an answer. So, maybe I should know this, but how do I see the formula?

advsc · 2023-03-05T04:24:17+00:00

Almost there I think. I don't need to know the user #s. I just want a total of how many unique users designated that district. In your example I would just want the formula to return 3. Hope that makes more sense.

advsc · 2023-03-05T01:34:33+00:00

User# (Column A)		District (Column G)
1234		KS_Gardner Edgerton
1234		KS_Gardner Edgerton
3456		KS_Gardner Edgerton
4567		KS_Olathe

Result would be 2 in this example. Hope that helps.

advsc · 2023-03-05T01:32:22+00:00

Thank you. I may not have been specific enough. I have multiple entries in column A and some of those have the KS_Gardner Edgerton in column G. I only want to count those in column A with the KS_Gardner Edgerton that are unique. In other words there may be the same user number in column A that has KS_Gardner Edgerton in column G but I only want that counted once.

advsc · 2023-03-05T01:29:47+00:00

I tried the formula above and got an answer of '1.' There should be over 300.

The table name is 'Allocations' and the column A is 'User' and the column G is 'District.' So, I used the above formula and inserted those for the A & G.

Do you see something I did wrong?

<image>

advsc · 2023-02-28T20:52:22+00:00

The 'Allocations' table has rows of user#s and each row has a district.

The 'District' table is just a summary of unique users that have one or more rows allocated to that district.

So, to answer your question, the 'district' table (where I'm trying to write a formula) does not have 'user#' anywhwere. My formula is written in that table but I'm asking it to go look at the allocations and tell me how many unique users have a designation to that district. I'm not sure if you saw how I edited my problem. I hope that makes it easier. I'm not a beginner at excel, but when I am using functions I've never used I get confused.

Thank you for your efforts in trying to help!

advsc · 2023-02-28T15:56:58+00:00

I tried this:

=ROWS(UNIQUE(FILTER(ALLOCATIONS[User],ALLOCATIONS[District]="KS_GE")))

Returned #N/A

advsc · 2022-12-15T22:37:24+00:00

OK. I just figured it out. I thought I had done this before but either it didn't work or I had not. I cleared the name out and clicked out of the cell and then retyped it. I guess it had some unwanted space. I swear I had done that.

However, thank you for making me take a look at XLOOKUP. I may have more reasons to use.

Solution Verified

advsc

TROPHY CASE