Log In
Register
Home
September 13, 2020
Offline
Can someone help me with formulas with questions in Excel file attached here please?
(PS Excel formula, not Python please)
Thanks in advance!
See Answer
Trusted Members
Moderators
January 31, 2022
Offline
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?
VIP
Trusted Members
December 7, 2016
Offline
Hello,
There is no need for complex formulas if you just rearrange the data. A Pivot Table would be my choice.
Br,
Anders
September 13, 2020
Offline
@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.
Trusted Members
Moderators
January 31, 2022
Offline
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?
Trusted Members
Moderators
November 1, 2018
Offline
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.
Answers Post
September 13, 2020
Offline
September 13, 2020
Offline
Velouria,
Yes! That is! Thanks!
New Member
November 22, 2023
Offline
=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
New Member
November 22, 2023
Offline
=SUMIFS(B3:B17,F3:F17,"notebook") for Irene
=SUMIFS(c3:c17,F3:F17,"notebook") for John
Swap "notebook" for "desktop" in lower Q's
September 13, 2020
Offline
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!
1 Guest(s)
