SUMIF, AND OR WHATEVER

N

NILELATOR

I HAVE A SPREAD SHEET WITH 9 COLUMNS OF DIFFERENT INFORMATION BUT SPECIFIC TO
EACH ROW, STARTING WITH ROW 7. I NEED TO TOTAL ALL ROWS OF COLUMN E (THE
VALUE) , BASED ON MATCH WITH CELL C5 (LIST OF NAMES) TO ANY MATCHING ROW IN
COLUMN A (NAMES), BUT ALSO NEEDS TO MATCH EITHER "PEND", "yES", "NO" IN
COLUMN H.
IT NEEDS TO ONLY COUNT CELLS IN COLUMN E, IF BOTH CRITERIA'S IN COLUMN A AND
H HAS BEEN MET
 
R

Roger Govier

Hi

Try
=SUMPRODUCT(($A$7:$A$1000=$C$5)*($H$7:$H$1000="PEND")*($E$7:$E$1000))

For your future reference
Please do not post in capitals, it is regarded as "shouting" in the
Newsgroups, and is very difficult to read.
 
T

T. Valko

Please turn off your caps lock!

Try this:

=SUMPRODUCT(--(A7:A17=C5),--(ISNUMBER(MATCH(H7:H17,{"pend","yes","no"},0))),E7:E17)

Biff
 
N

NILELATOR

Thank you that works, I didn't know about the caps ment shouting, i'm getting
old and it helps to see.
what does the "*" inbetween the formulas do?
 
R

Roger Govier

Hi
what does the "*" inbetween the formulas do?
Click to expand...
There are 2 sets of tests being carried out and 1 set of values
resulting in 3 arrays.
Does column A = value in C5, True or False
Does column H contain "Pend", True or False
And the data in column E

The "*" is use to multiply the ranges together, which coerces the True's
to 1's and False's to 0's so when the 3 arrays are multiplied together
you get something like
0 x 1 x 50 = 0
1 x 0 x 25 = 0
1 x 1 x 30 = 30
and so on down the 994 rows in the range 7:1000.
Sumproduct then adds all these values to give the answer. (in this small
example, 30)

The alternative is to use the double unary minus "--" to coerce the
True/False to 1/0 and just use a "," between each of the sets.
This was the method Biff did in his later posting.

Biff also had a different take on your request, and has given a solution
which returns a True if column H contains either "Pend" or "Yes" or "No"
whereas I had read it that you would want to run all 3 separately and
have the totals for each case by Changing "Pend" to Yes or No in
different formulae.
--(ISNUMBER(MATCH(H7:H17,{"pend","yes","no"},0)))
where if Match returns a number for any of the three values in the array
{ }, then it returns a 1.
 
R

Roger Govier

Hi

If your names are in column A and Dates in column B and value to be
summed is in column E,
then if you place the Name you are seeking in A5 and the month number in
C5

=SUMPRODUCT(($A$7:$A$1000=$A$5)*
(MONTH($B$7:$B$1000)=$C$5)*($E$7:$E$1000))

But you would be far better off with a Pivot Table. I assume you have a
header row in row 6, with titles for each column
First create a Dynamic Range.
Insert>Name>Define>Name Mydata >Refers to
=OFFSET($A$6,0,0,COUNTA($A$7:$A$1000),9)

Place your cursor in row 6, Data>Pivot Table>Next>Source =Mydata>Finish
On the new sheet created,
Drag the Date field to the Row area
From the PT toolbar, use the dropdown to select >Group and Show
Detail>Group>Month
Then drag the newly grouped Date field to the Column area instead.
Drag the Name field to the Row Area
Drag the Amount field to the Data area.
Drag your filed heading for column H to the Page Area

Now you can select Pend or No or Yes from the dropdown on Page field and
see the data summarised by Name and by Month.

For more information on Pivot Tables take a look at the following sites
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html

http://www.datapigtechnologies.com/flashfiles/pivot1.html

http://www.edferrero.com/Tutorials.aspx
 
R

Roger Govier

Hi

You need to have highlighted the Date field before trying to Group.
If it comes up with the message "Cannot group by that Selection", then
either there are some invalid Dates within your date column, or there
are some blank cells. In either of these cases, Grouping cannot occur.
Check your source data.
 
N

NILELATOR

thank you that worked.

Roger Govier said:
Hi

You need to have highlighted the Date field before trying to Group.
If it comes up with the message "Cannot group by that Selection", then
either there are some invalid Dates within your date column, or there
are some blank cells. In either of these cases, Grouping cannot occur.
Check your source data.
Click to expand...
 
R

Roger Govier

You're very welcome. Thanks for the feedback letting us know it worked
out for you.
 
Top