SUMIF based on 2 columns instead of 1?

  • Thread starter Thread starter barbetta3141
  • Start date Start date
B

barbetta3141

I have columns Person ("Adam", "Bob", "Charlie"), Day ("Monday",
"Tuesday", etc), Total. I'd like to SUMIF based on BOTH Person and Day
(e.g. sum for "Bob" and "Tuesday"). I'd rather not use PivotTables,
because (1) this is pretty simple and (2) people here are scared of
PivotTables. Can i use SUMIF based on 2 columns, either by
concatenating the text or with an array? Thanks.
 
Hi there,

Try something like ..

=SUMPRODUCT((A1:A100="Bob")*(B1:B100="Tuesday"),C1:C100)

Where column A has your names, column B has your days and column C has the
associated value you wish to sum.

HTH
 
You can use SUMPRODUCT, e.g.

SUMPRODUCT(--(A2:A100="Bob"),--(B2:B100="Tuesday"),C2:C100)
 
Have you tried an array function, something like

=(SUM((A1:A3="Bob")*(B1:B3="Tuesday")))

then use ctrl shift enter to set it as array
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel Add 60 days to column 1 or column 2 based on whether column 2 has data 1
sumif help 1
Payroll,formulas and Macros? 2
special case of sumif 4
concatenate entries from a column into a cell by logical AND withanother column 2
Summing based on currency format of Cell 2
SUM columns based on 2 pieces of logic 2
SUMIF Help 1

Back
Top