can you nest sumif functions ?

[SCarleton]

I am trying to nest sumif function with no luck.
I have a table of data with several columns.
I need to look at the month column first and see if it's "January", then
look at the "name" column to see if a specific person performed the
procedures in that month, then finally add the number of procedures this
person performed in January.
I want to have a Data sheet that contains all this information, then without
using pivot tables set up a permanent spreadsheet that puts names of people
as rows and months as column headers, then the number of procedures as the
data.
Can anybody help ?

 

[Roger Govier]

Hi

Assuming that this data is held on Sheet1 and Column A holds Month, B
holds Name and C holds Count of functions.

On Sheet2 set up a Matrix with January in B1 through to December in M1
Place your Names in A2:Ann where nn is the last row for the range of
people concerned.

In B2 enter
=SUMPRODUCT((Sheet1!$A$2:$A$100=B$1)*
(Sheet1!$B$2:$B$100=$A2)*Sheet1$C$2:$C$100))

This assumes that your entries in column A of Sheet1 are text entries.
If they are true Excel Dates, then amend formula to
=SUMPRODUCT((TEXT(Sheet1!$A$2:$A$100,"mmmm")=B$1)*
(Sheet1!$B$2:$B$100=$A2)*Sheet1$C$2:$C$100))

Copy formula across and down to fill your matrix.
Change ranges in formula to suit range of data on Sheet1 but do ensure
all ranges are of equal length.

 

[Max]

Perhaps something along these lines:

In B1 across: January, February ...
In A2 down: Names of staff

Then in B2, copied across and filled down to populate:
=Sumproduct((Staff=$A2)*(Month=B$1),Procedures)

where Staff, Month, Procedure would be identically structured defined ranges
referring to the relevant source data