Creating Report

Jenn123

I want to compare QtyShipped in sheet 3.2.6.2 to the Qty in 3.2.6.1.

The QtyShipped in sheet 3.2.6.2 lists the quantities as we ship the parts and Qty in 3.2.6.1 is our contract requirement quantities.

Is there a way to sum up the quantities in the QtyShipped in sheet 3.2.6.2 and see that next to the Qty in 3.2.6.1. We want to figure out what quantity is left to ship.

3.2.6.1.png

3.2.6.2.png

Paul Newcome

It would be something along the lines of

=SUMIFS({Range To Sum}, {1st Criteria Range}, 1st criteria, {2nd Criteria Range}, 2nd criteria)

Each {Range} would be created by (when you get to that part of the formula) clicking the blue link to "Reference another sheet". You would then select the sheet you want to reference then (in this case) click on the appropriate column header.

Darren Mullen

@Jenn123 Yes, there is. You can use COUNTIFS() with cross sheet references then match based on the item identifier.

Jenn123

Do I do the COUNTIFS() in the sheet or the report?

Paul Newcome

You would use a SUMIFS on 3.2.6.1.

=SUMIFS({3.2.6.2 Qty}, {3.2.6.2 Part #}, @cell = [Part #]@row)

Make sure to create the cross sheet references and label them how you see fit and also make sure the [Part #] column reference matches what you are actually using in 3.2.6.1.

Jenn123

That didn't seem to work. Here is a better view of what I'm working with. I want to total the QtyShipped in 3.2.6.2 by ProjNo and LineItem so I can compare that quantity to 3.2.6.1 ContractQty

3.2.6.1.png

3.2.6.2.png

Paul Newcome

If it is ProjNo and LineItem, you would use those range/criteria sets in the SUMIS instead of the Part Number.

Jenn123

can you help me put that equation together? I don't know much about range/criteria sets in Smartsheet yet.

Paul Newcome

It would be something along the lines of

=SUMIFS({Range To Sum}, {1st Criteria Range}, 1st criteria, {2nd Criteria Range}, 2nd criteria)

Each {Range} would be created by (when you get to that part of the formula) clicking the blue link to "Reference another sheet". You would then select the sheet you want to reference then (in this case) click on the appropriate column header.

Jenn123

This is helpful, but I'm getting stuck on what to put for the 1st criteria and the 2nd criteria in the equation.

Jenn123

I figured it out.

Georgie

Glad to hear you figured this out @Jenn123 - could you post the working formula here to help other members reviewing this thread?

Thanks,

Georgie