can you nest sumif functions ?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 ?
 
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.
 
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
 
Back
Top