excel training courses - sumif two variables

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » excel training courses - SUMIF - Two Variables

excel training courses - SUMIF - Two Variables

resolvedResolved · Low Priority · Version Standard

Heather has attended:
Excel Intermediate course
Excel Advanced course
Outlook Advanced course
PowerPoint Intermediate Advanced course
Word Advanced course

SUMIF - Two Variables

how do i use the SUMIF function for two variables. i.e. i want to sum the values of products that were purchased between march and april AND were blue.

RE: SUMIF - Two Variables

Hi Heather,

I don't know what the trainers would do, but I'd use 2 columns to sort this out, then hide them to keep my spreadsheet tidy

let's say for example your dates purchased are in column D and your colours are in column E.

In row 1 of column G type this:

=IF(AND(D1>=39142,D1<=39202),1,0)

this will return a 1 if the date in cell Dx is between March 1st and April 30th

then in row 1 of column H, type this:

=IF(AND(E1="blue",G1=1),1,0)

this will return a 1 if cell Gx = 1 and if cell Ex = blue

then you can sum up column H and you have the total number of "blue" bought in March & April


 

Excel tip:

Copying Formulae Quickly

There is a quicker way of copying a formula down a column.

Just point and double click on the black autofill handle in the right hand corner of a cell.

This will work providing you have some data in the column to the left of the column.

Sandy

View all Excel hints and tips


Server loaded in 0.09 secs.