What formula do I use to get this data please?

Question

Hope you can help me. I help with a food bank and we are trying to get totals for the number of parcels delivered last year. There are two types, Single and Family, and I can't figure out how to add up the total parcels received by a person to get a total of all the single/family parcels we delivered. Those figures are not recorded weekly.

So in the image below IZ is the number of parcels a person received, collected etc, and I've changed the text Single and family to 1 or 2. What formula will give me all the single and family parcels delivered for the whole year? I feel like this is an easy answer but I'm not very skilled at Excel and can't figure it out!

Thanks in advance

Answer 1

Hello Laura Mathieson,

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

I understand that you have confusion when using Excel formula. That's very understandable and believe me you're not alone. I am here to help you out.

Thank you for your description, But the SUMIF argument order is a bit different from what you typed. Please check below, that's my suggestion and additional information for you:

The quick fix (no date filter)

If IZ holds the number of parcels and JA holds the type code (1 = Single, 2 = Family), use SUMIF like this:

' Total Single parcels (type code 1)
=SUMIF(JA:JA, 1, IZ:IZ)
' Total Family parcels (type code 2)
=SUMIF(JA:JA, 2, IZ:IZ)

In SUMIF(range, criteria, sum_range), range is where Excel checks the condition (here: JA:JA), and sum_range is what gets added up (here: IZ:IZ).

Your draft =SUMIF(IZ1:IZ10,1,JA1:JA10) flips those two ranges, so Excel would try to test the Parcel totals column for “=1” and then sum the Type codes—producing the wrong result. The corrected versions above follow Microsoft’s documented argument order.

Add a “last year” filter (recommended)

If you also track a Date for each delivery (say in column H), switch to SUMIFS so you can include date criteria:

' Single parcels delivered last calendar year (example uses 2025—change to your year)
=SUMIFS(IZ:IZ, JA:JA, 1, H:H, ">=1/1/2025", H:H, "<=12/31/2025")
' Family parcels delivered last calendar year
=SUMIFS(IZ:IZ, JA:JA, 2, H:H, ">=1/1/2025", H:H, "<=12/31/2025")

SUMIFS sums IZ:IZ where JA:JA equals the type code and the H:H dates fall in the specified range. This is the supported, built‑in way to sum with multiple conditions.

If you ever need counts instead of sums:

To count how many Single vs. Family entries (regardless of parcels), use COUNTIFS:

' How many Single rows last year
=COUNTIFS(JA:JA, 1, H:H, ">=1/1/2025", H:H, "<=12/31/2025")
' How many Family rows last year
=COUNTIFS(JA:JA, 2, H:H, ">=1/1/2025", H:H, "<=12/31/2025")

This matches multiple criteria across ranges and returns a count. For more information, please check this page: https://support.microsoft.com/en-us/office/sumifs-function-c9e748f5-7ea7-455d-9406-611cebce642b

I hope this will help with your situation. Please feel free to reach back if you have further update or more questions.

Best Regards,

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment”.   

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.

Answer 2

Hi,

You may create a Pivot Table. Drag column JA to the row labels and column IZ to the value area section.