Substitute for Sumfs formula with 1 row and 1 column for Criteria

**Squint** · 01-27-2015, 04:23 PM

I am hoping someone can help. I am working on a spreadsheet that uses 2 criteria to sum but one is a culumn and 1 is a row and 1 is a column. I am attaching a copy of the spreadsheet. I want a formula on the Summary tab that will separate the the total for each classification if it is either a project or service as shown in row 2 on the FTEs tab. I can't find a formla to work so if there are any suggestions I would so appreciate any help.

Thanks in advance

**daffodil11** · 01-27-2015, 04:44 PM

SUMIFS works fine, but SUMPRODUCT is a little easier to troubleshoot, due to being able to step through the evaluation process.

SUMPRODUCT

Please Login or Register  to view this content.

SUMIFS

Please Login or Register  to view this content.

for either, copy down and across as needed

**Pete_UK** · 01-27-2015, 04:52 PM

You can use this formula in C2 of your Summary sheet:

=SUMPRODUCT(('FTE''s'!$B$3:$B$84=A2)*('FTE''s'!$Q$2:$U$2=$C$1),'FTE''s'!$Q$3:$U$84)

and this one in D2:

=SUMPRODUCT(('FTE''s'!$B$3:$B$84=A2)*('FTE''s'!$V$2:$AC$2=$D$1),'FTE''s'!$V$3:$AC$84)

and then copy down. NOTE that you do not have an entry for TE or for YW in your summary table.

Hope this helps.

Pete