Forum

Notifications
Clear all

Two tables - formula needed (link provided to Excel file with examples and questions)

11 Posts
5 Users
0 Reactions
84 Views
(@neshas)
Posts: 26
Eminent Member
Topic starter
 

Can someone help me with formulas with questions in Excel file attached here please?

(PS Excel formula, not Python please)

 

Thanks in advance!

 
Posted : 18/11/2023 12:23 pm
Riny van Eekelen
(@riny)
Posts: 1198
Member Moderator
 

Could you please clarify? Perhaps a language issue but when you ask "how much notebooks/desktops", do you mean how many (i.e. counting quantities) of how much as in total sales amounts? Or perhaps both? And what Excel version are you using?  

 
Posted : 20/11/2023 11:29 am
Anders Sehlstedt
(@sehlsan)
Posts: 972
Prominent Member
 

Hello,

There is no need for complex formulas if you just rearrange the data. A Pivot Table would be my choice.

Br,
Anders

 
Posted : 20/11/2023 2:27 pm
(@neshas)
Posts: 26
Eminent Member
Topic starter
 

@Riny van Eekelen

Yes, "how many" should be, sorry for the confusion. Looking for SUM of numbers.

For instance, Irene sold 19 notebooks, and that is one number I'm looking for with a formula.

 

Excel 365.

 

@Riny van Eekelen, yes I know it could be done with Pivot, I have that already, but for this case, I'm trying to solve this with a formula.

 
Posted : 21/11/2023 4:50 am
Riny van Eekelen
(@riny)
Posts: 1198
Member Moderator
 

Although Anders mentioned that this could be done with a pivot table, his file included a formula solution with SUMIF.

Attached file does include a power pivot (table) solution. Since you have two separate tables joined by the Model reference, you can relate the two in a small data model and create a pivot table from there.

Edit: I now see that you want a formula solution anyway. Will Anders' SUMIF work for you?

 
Posted : 21/11/2023 5:45 am
(@debaser)
Posts: 837
Member Moderator
 

Without rearranging the data, you can do something like:

=SUMPRODUCT((XLOOKUP(A3:A17,E3:E17,F3:F17)="notebook")*B3:B17)

to add up Irene's notebooks, for example.

 
Posted : 21/11/2023 10:25 am
(@neshas)
Posts: 26
Eminent Member
Topic starter
 
@Riny van Eekelen
 
Anders didn't get right results. Irene sold 19 notebooks. He overlooked A column from "Sales by salesman - QTY" from my original file.
 
I've seen your solution, but I need formula this time, as I've stated in my first post.
 
Posted : 22/11/2023 3:23 am
(@neshas)
Posts: 26
Eminent Member
Topic starter
 

Velouria,

 

Yes! That is! Thanks!

 
Posted : 22/11/2023 3:24 am
(@oreight)
Posts: 2
New Member
 

=SUMIFS(B3:B17,F3:F17,"notebook") for Irene
=SUMIFS(c$3:c17,F$3:F$17,"notebook") for John

Swap "notebook" for "desktop" to get desktop total 

 
Posted : 22/11/2023 11:17 am
(@oreight)
Posts: 2
New Member
 

=SUMIFS(B3:B17,F3:F17,"notebook") for Irene
=SUMIFS(c3:c17,F3:F17,"notebook") for John

Swap "notebook" for "desktop" in lower Q's

 
Posted : 22/11/2023 11:19 am
(@neshas)
Posts: 26
Eminent Member
Topic starter
 

Gordon Cooper

 

Thanks for your effort and answer, but I already got one form Velouria. Check his formula.

Regarding your formula, it doesn't sum good results. For instance, Irene sold 19 notebooks, not 43 as it sums up with your formula.

Thanks anyway!

 
Posted : 23/11/2023 4:14 am
Share: