Help with SUMIFS

[Katie Wine]

Hello,

My first time using SUMIFS, based on what the function is meant to do,
think this is the function I should be using, but just can't seem to ge
it to work.

I have data on my Master tab, and attempting to total on my Ship Summar
tab. The issue may be that I am not referencing the other tab correctly


Sum Range: Sum the numbers in column P in the Master tab
The criteria I need is:

1) If the client number in column AL in the Master tab equals the clien
number in column B in the Ship Summary tab

2) If the text in column Q in the Master tab equals "Bottles"

Currently I have the below formula entered in cell G5 in the Shi
Summary tab:
=SUMIFS(Master!$P$2:$P$600, Master!$AL$2:$AL$600, B5
Master!$Q$2:$Q$600, "Bottles")

It returns a count of 0, though for this cell it should return a coun
of 12.
I'm having trouble uploading my file, if it helps to see it, pleas
advise how I can provide it for viewing.

Thanks to advise,
Kati

 

[joeu2004]

I'm having trouble uploading my file, if it helps to see it,
please advise how I can provide it for viewing.

Yes, we might need to see it unless we get lucky with a wild-a.s.s guess.

Have tried to upload to a file-sharing website? The following is a list of
some free ones. IMHO, box.net/files is very easy to get started with and to
use. Just remember to click on "Share", copy the URL and post it in a
response here.

Box.Net: http://www.box.net/files
Windows Live Skydrive: http://skydrive.live.com
MediaFire: http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
FileDropper: http://www.filedropper.com
RapidShare: http://www.rapidshare.com

=SUMIFS(Master!$P$2:$P$600, Master!$AL$2:$AL$600, B5,
Master!$Q$2:$Q$600, "Bottles")

It returns a count of 0, though for this cell it should
return a count of 12.

The syntax looks okay off-hand. A common cause of such problems is: the
date in P2600 is text, not numeric. What does =TYPE(P2) return? Well,
choose a cell in column P that matches your criteria.

Of course, another possibility is: AL2:AL600 and Q2:Q600 do not actually
match the criteria. So many possible ways that can happen. Leading and
trailing spaces or non-breaking spaces (HTML &nbsp). That's why we might
need to see the file.

 

[julied d]

Hi Katie

That should work.

I would check that the formatting of your customer number in the
Master sheet and the Ship Summary sheet is the same, (ie both text or
both numbers, you can use the =TEXT(cell_reference) or
=NUMBER(cell_reference) to check)

Cheers
Julie