How to fix this issue i am getting with Sum and trying to add a qty based on a budget number

Cole Frank

I want to sum a number of quantities based on a criteria that checks the budget number to the row in the purchase request sheet that has the same budget number to then add all those quantities up in a cell in the budget so this way Each budget line that is used in a purchase request the qty of how many items purchased is automatically added into the budget to see how much we have left to spend from that budget line

Genevieve P.

Hi @Cole Frank

If you have different columns that could have different information on the same row, you'll want to create multiple SUMIFS statements for each pairing and add those together with the + symbol like above:

=SUMIFS({QTY 1 Column}, {Budget Number Column 1}, [EBN/PO]@row) + SUMIFS({QTY 2 Column}, {Budget Number Column 2}, [EBN/PO]@row)

Does that make sense?

Colleen Patterson

@Cole Frank

Can you drop in some screenshots of what you are trying to sum?

Cole Frank

Here is the sheet i want the qty from and the budget number

Here is the sheet i want to sum the qtys together

Genevieve P.

Hi @Cole Frank

Try using SUMIFS:

=SUMIFS({Column to SUM}, {First Criteria Column}, [1st Criteria]@row, {Second Criteria Column}, [2nd Criteria]@row)

Or in your case, something like:

=SUMIFS({QTY 1 Column}, {Budget Number Column134}, [EBN/PO]@row)

See: SUMIFS Function and Create cross sheet references to work with data in another sheet

Cheers,

Genevieve

Cole Frank

Thank you so much, this was stressing me out the past couple of days trying to figure it out

If i wanted to do this for multiple different columns could i continue the sumifs function?

Genevieve P.

No problem, @Cole Frank! Happy to help 🙂

When you say "do this for multiple columns", do you mean add together multiple columns or use multiple criteria?

You can add multiple SUMIFS together with a + symbol, like so:

=SUMIFS({QTY 1 Column}, {Budget Number Column134}, [EBN/PO]@row) + SUMIFS({QTY 2 Column}, {Budget Number Column134}, [EBN/PO]@row)

NOTE: if you copy/paste the first formula, would need to delete out the first reference and replace it with the new column to SUM as a new reference. Don't edit the old reference or it will update the previous part of the formula as well.

Cole Frank

So on the purchase request sheet that I have, I broke down items on a quote into all separate columns because each line on a quote has a different unit cost and budget line for that item.

So if I have EBN-231-39400 in column 1 and a qty of 2 and then in column 2 I have EBN-231-39400 next to it with a qty of 5

How in the budget sheet can I reference all the different columns? To add together in the budget for qty.

Also, my plan is to do this with the total cost but I can just reverse-engineer this solution

Genevieve P.

Hi @Cole Frank

If you have different columns that could have different information on the same row, you'll want to create multiple SUMIFS statements for each pairing and add those together with the + symbol like above:

=SUMIFS({QTY 1 Column}, {Budget Number Column 1}, [EBN/PO]@row) + SUMIFS({QTY 2 Column}, {Budget Number Column 2}, [EBN/PO]@row)

Does that make sense?

Cole Frank

Yes it does; thank you so much.

Cole Frank

@Genevieve P. How would I go about making it only calculating rows that have been ordered, would I have to make it for each line item, or can I just have it be for the entire row?

Genevieve P.

Hi @Cole Frank

Since you're evaluating the columns separately, you would need to add in that criteria for each of the individual SUMIFS statements, like so:

=SUMIFS({QTY 1 Column}, {Budget Number Column 1}, [EBN/PO]@row, {Order status column}, "Ordered") + SUMIFS({QTY 2 Column}, {Budget Number Column 2}, [EBN/PO]@row, {Order status column}, "Ordered")

Cole Frank

Thank you so much