SUMIF

  • Thread starter Thread starter Karina
  • Start date Start date
K

Karina

Hi all

I would really appreciate any help you could give me. I am
using Pivot tables for some financial reports. However I
also need to use SUMIF on the same spreadsheet to sum
certain ranges on the pivot table.
For example some of our sales codes are 1000, 1100, 2000,
2300 etc. I need to be able to sum the B column depending
on the range in the A column.
I am using =SUMIF(A1:A20,"<2000",B1:B20) and I get 0,
which is not right. Also when I use the same formula but
use an '=' instead of the '<' I get the right answer.
These formulae both work when I use them on cells not in a
pivot table.

Thanks a mill
 
Hi Karina!

Try:

=SUMIF(A1:A20,"<"&2000,B1:B20)

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi Karina!

It worked for me. Do you want me to send you the sheet? Email direct
and I will

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi Karina!

Problem in workbook sent was that the column A data were not numbers
but text.

Since they were imported from a remote source into a pivot table the
solution adopted was to copy elsewhere and convert to numbers.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Problem in workbook sent was that the column A data were not numbers
but text.

Since they were imported from a remote source into a pivot table the
solution adopted was to copy elsewhere and convert to numbers.
Click to expand...
...

And you needed the workbook sent to you to figure this out?!

What's the most frequent source of lookup/match errors when the lookup values
are or appear to be numbers?

By getting mentally lazy and asking for worksheets rather than leaving such
problems to others with perhaps more experience and understanding you encourage
people to attach files to their postings, something generally viewed as
undesirable. Thanks a lot!
 
I dont know why, but your replys seem to come up as new threads beginning
with 'Re:' as you post them, instead of falling underneath the original
thread like replies from other people do.
 
Hmm. It just shot down to where it belonged. Nevermind, have to figure out
why that's happening.
 
Hi Harlan!

I don't agree. And it was a send direct to me invitation not a request
for an attachment to a post.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
I don't agree. And it was a send direct to me invitation not a request
for an attachment to a post.
Click to expand...

Granted, but any naive reader of this newsgroup could mistakenly get the
impression that sending the workbook was essential to getting an answer.
Therefore, it wouldn't be an unreasonable conclusion that attaching the file in
the first place in the original posting would result in a quicker answer.

Do you really need this chain of reasoning spelled out in detail?
 
Hi Harlan!

You answer your way. I'll answer mine.

Several issues came up and Karina was a very satisfied "customer".
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
You answer your way. I'll answer mine.

Several issues came up and Karina was a very satisfied "customer".
Click to expand...

Obviously you lack some understanding of how newsgroups operate. It's nice but
not sufficient to satisfy the OP. The ideal which some of us can achieve is to
answer the OP in such a way that it doesn't lead to adverse consequences in
other, subsequent threads. Such as encouraging other people to attach files in
the mistaken belief that doing so would be expedient.

Looking at the workbook should be a last resort. Guess some reach the last
resort earlier than others.

If you don't understand this now, you probably never will.
 
I wouldn't call my knowledge of Excel "working" but I have an urgen
problem.

I have a set of worksheets with sales data:

Name(text) Date Paid(date) Amount($)

I have successfully created names for these ranges / columns, and I ca
extract data from one sheet and place in another, using SUM IF to us
the "Name" column as the criteria, and sum up the corresponding value
in the Amount Column.

My problem is I want to further calcualate the sum based on the dat
paid, eg: if date paid is Feb. 14, then go ahead, otherwise skip it
That way I can total sales for a particular name by month paid.

Right now the formula looks like this:

SUMIF(AGENCY1,A22,TOTAL1)

where AGENCY1 is a list of names for one worksheet, A22 is a cell o
the current worksheet with a name that should be matched, and TOTAL1 i
the corresponding cell for the name in the Amount column.

Please help, I am stuck at this point.

Thanks in advance
 
Hi
as SUMIF only accepts one condition you may try the following
=SUMPRODUCT(('sheet1'!A1:A1000=A22)*('sheet1'!B1:B1000=DATE(2004,2,14))
,C1:C1000)

or you may consider using a pivot table
 
The pivot table was brilliant idea!! I don't know why I never thought o
that. But now I need to get everything into a range of dates (b
month). The pivot table gives me each date in the worksheet.

Your help would we once again greatly appreciated.

Thanks a million, you can't begin to imagine how you helped me ou
there....
 
Hi
a simple way:
add a helper column to your source data with the formula
=TEXT(A1,"MMM")
if column A stores the dates. Copy this down and use this helper column
in your pivot table instead of the dates
 
Back
Top