sum using multi components across multi sheets

Question

Good morning,

I'm having a small brain fart on how to get this done.

I pull a report from a program every morning and I need to import the quantity into another sheet based off of Column (A) and weather Column (B & C) is checked and the quantity from Column (G) into the other sheet. I would like to Place an x in Column B or C depending on if it was a inbound or outbound call if possible.

So based on what I'm trying to achieve, Column (O) Cell (7) should have a total of 7

Thank you for any help you offer.

Answer 1

Hi @KARL HEINE,

Thank you for posting your question in the Microsoft Q&A forum.

According to your concern, I want to confirm whether the output in O7 would be 7 or 2? In your sample data, the employee 33129 only has two rows with quantity 1 of item 420971101 so based on your conditions the output in O7 should be 2 instead.

If that's the case, you can use this formula:

• First, I highly recommend converting your source data into Excel table for better formula checking. Select your data range > press Ctrl + T > choose My table has headers > OK. Then name it Daily and you can edit the layout via Table Design tab > Table Style

• In the output sheet, to achieve your expected result in cell O7, use this formula:

    =SUMIFS(Daily[Quantity], Daily[Employee], $A7, Daily[Item], $N$3, Daily[IN], "x")
  

• When you go back to the daily sheet and insert "x" into column C, the output result will become 2.

• You can use the same formula for other cells (like K7, L7, N7, etc.). However, please note that when using formula with table fields like this one, you can only drag the formula up and down. If you drag the formula to the left or right, the fields will be re-arranged, and your result can be calculated wrong.
• If you don't prefer using table, you can keep the source data as normal range and the formula will be like this:

    =SUMIFS(Daily!$G$2:$G$14,Daily!$A$2:$A$14,$A7,Daily!$F$2:$F$14,$N$3,Daily!$C$2:$C$14,"x")
  

Hope this information will help. If you have any questions or need further assistance, please feel free to share them in the comments on this post so I can continue to support you.  

Thank you for your patience and understanding. Looking forward to your response.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it.      

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.