Nesting SUMPRODUCT in SUMIF

Attach example EXCEL file (not a picture, pasted text or any other than Excel format!). This file should be attached to a new post. Never change your original post (except admin's request).

To attach an Excel file to your post,

• desensitize data
• remeber that your example should reflect structure and type of data and contain the result what you want to achieve (manually created if necessary)
• click Go Advanced,
• scroll down until you see Manage Attachments,
• click that and select Browse,
• select your file and click Open,
• click Upload and you will see your attachment below Upload Files from a website
• click Close this window,
• click Submit reply

After that you should see attachment in your post

Thanks, Sandy!
Example excel file has been attached.

Instead of formula you can try PowerQuery aka Get&Transform. THis feature is built-in to Excel 2016 Pro.

PowerQuery does not contain any worksheet function or VBA code.

Sanday666, thank you for your help! I unfortunately don't have Excel 2016 Pro, so PowerQuery is not activated.

Thinking out of the box

=SUM(SUMIFS($G$2:$G$12,$B$2:$B$12,$I3,$C$2:$C$12,"FTE"))

=SUM(SUMIFS($G$3:$G$13,$B$2:$B$12,$I3,$C$3:$C$13,"PTE"))

Note that the ranges in columns G and C are moved down by 1 row in the second formula. This is to allow for the merged cells in column B.

Thanks all for your help.
I ended up using the formula posted by kvsrinivasamurthy.

I have another lay of this problem and have attached an updated spreadsheet.
If the territory has an assistant manager, I don't want those quarter's employees to be counted in the total.
For example: row 3 is Joe Small's Northbay employees.
In Q1, he did not have an assistant manager, but did in Q2.
The totals in cell J4 should total 3 but the totals in cell K3 should total 0.

Originally Posted by ngocdinh

I ended up using the formula posted by kvsrinivasamurthy.

Have a read of this https://chandoo.org/wp/handle-volati...-are-dynamite/

To answer your latest question.

In J4

=SUM(SUMIFS($G$2:$G$12,$B$2:$B$12,$I3,$C$2:$C$12,J$2,$F$2:$F$12,"="))

In K4

=SUM(SUMIFS($G$3:$G$13,$B$2:$B$12,$I3,$C$3:$C$13,K$2,$F$3:$F$13,"="))

Jason.b75, Thank you very much for your reply.
I didn't not explain myself properly.

If in a quarter, the manager has an assistant manager, but total employee count for that quarter should include the assistant manager, but not no employee reporting underneath that manager.

In the example file I attached:
Billy Jones' employee count for Q1 should equal 4, and Q2 should equal 1.

To be sure I'm following the logic correctly now, Joe Small Q2 = 4 (Northbay assistant manager x 1, Eastbay sales associate x 2, Eastbay supervisor x 1) is that correct?

See if this gives the correct results.

In H2, enter this formula, then fill down to H12

=IF(F2>0,F2,G2)

In J3, and fill down

=SUMIFS($H$2:$H$12,$B$2:$B$12,$I3,$C$2:$C$12,J$2)

In K3 and fill down

=SUMIFS($H$3:$H$13,$B$2:$B$12,$I3,$C$3:$C$13,K$2)