Can someone help me with formulas with questions in Excel file attached here please?
(PS Excel formula, not Python please)
Thanks in advance!
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?
Hello,
There is no need for complex formulas if you just rearrange the data. A Pivot Table would be my choice.
Br,
Anders
@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.
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?
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.
Velouria,
Yes! That is! Thanks!
=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
=SUMIFS(B3:B17,F3:F17,"notebook") for Irene
=SUMIFS(c3:c17,F3:F17,"notebook") for John
Swap "notebook" for "desktop" in lower Q's
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!