Totalling seperate cells from a HLOOKUP function

S

SimonSNA400

is it possible to sum the product of 2 or more cells, 1 cell being the result
of
a HLOOKUP function, without creating a third cell for individual results?
Here is my problem:
Row A contains a list of different locations
Row B contains wether the delivery has failed or completed
Row C contains the quantity of items on Invoice

Is there a formula I can use to have the total quantity from all of the
failed deliveries for that day?

As the failed delivery locations may vary from day to day, I cannot just
total those cells from each location

I have tried this formula, but it returns '#VALUE!':
=HLOOKUP("F",C4:L7,SUMIF(C4:L4,TRUE))

Any suggestions?
 
T

T. Valko

Try something like this...

B2:J2 = F (failed) or C (completed)
B3:J3 = quantities

=SUMIF(B2:J2,"F",B3:J3)
 
C

Crashin

I'm working with something similar to this post, not sure how to start a new
one, "New Thread" doesn't seem to be an active link for me.

Anyhow, I'm using columns that are consistent, have a header row at top and
total row at bottom, which updates itself automatically, based on any column
filters in use.

P2:p100 = textA
N2:N100 = quantities
M2:M100 = textB
L2:L100 = textC

I'm currently using
=SUMIF(P2:p100,"Refuelling with JetA",N2:N100)
for my first set of fields, with great success, updating formula with the
correct text for the other sections I'm wanting totalled, independently.

Now I've run into a case where I need a secondary condition checked, if the
first is met.

=SUMIF(P2:p100,"Group payment",N2:N100)
However, this can bet met with both incoming and outgoing payments, and
currently, that field cannot differentiate the two.
TextB and/or TextC determines if it's incoming or outgoing.

What I don't know, is how to add the second check.
I'll be using one of each. I'm thinking I'll need to add an AND or OR to
make it work, but nesting these contexts is something I'm unsure of.
SUMIF (A) should total only things that meet "Group payment" in column P,
and should sum all amounts in column N only if column L = "SoCal Airways"
Likewise,
SUMIF (B) should total only things that meet "Group payment" in column P,
and should sum all amounts in column N only if column M = "SoCal Airways"

One last thing, I know P2:p100 is including only rows 2 through 100, how can
I make that all-inclusive, as the sheet loads from an .xml output, and before
too long, it will have more than 100 rows. (Another related sheet is about
to break 10,000 rows)


Thanks for all the people that help out. I've learned loads just reading
through others' questions and responses. It's appreciated.
 
T

T. Valko

Try something like this for multiple conditions.

I like to use cells to hold the criteria.

A1 = Group payment
B1 = SoCal Airways

=SUMPRODUCT(--(P2:p100=A1),--(L1:L100=B1),N1:N100)

As far as the size of the data range increasing over time, just increase the
size of the referenced ranges:

=SUMPRODUCT(--(P2:p1000=A1),--(L1:L1000=B1),N1:N1000)

Or, use dynamic ranges:

http://contextures.com/xlNames01.html#Dynamic
 
C

Crashin

That appears to be working correctly:
=SUMPRODUCT(--(P2:p99999="Refuelling with
JetA"),--(S2:S99999="N208SA"),N2:N99999)

Is there a way to make this all work with =SUMIF instead of =SUMPRODUCT ?
Using SUMPRODUCT, if the range includes any empty cells, the result is
always #VALUE

I tried another route, using:
=SUM(IF(($P2:$P24="Refuelling with JetA")*($S2:$S24="N208SA"),N2:N24,""))
but with that I was getting #N/A or #VALUE when the range included any empty
cells, forcing me to update the formula each time the number of rows
increases (far too often to be reasonable going through and changing the
formula).
The P99999 seems to be working properly with SUMPRODUCT at this point, but
I'd had similar issues in the past (may have been my own error).

Thanks for the idea of having static cells with the conditional criteria.
I've made a new sheet simply for that purpose, wasn't too tough to change
formulae to reference the sheet/cell needed.

As it stands now:
=SUMPRODUCT(--(P2:p99999=strings!A21),--(S2:S99999=strings!A44),N2:N99999)
is providing accurate results, and is the base formula for which changes are
being derived from.

Looks to be a point where I can get a lot more done for now, thanks a ton!

[original left-overs after edit]

Sorry to change variables in the middle there... There's basically one
formula that will work for all of them, just taking out the parts I don't
need.

What I'm looking for with the range question, is how to build the formula
correctly, and not have to go back in and edit it each time the number of
rows increases (very very frequent) - That may work with a dynamic range, but
that's secondary to getting the formula to pull the right information,
nothing more, nothing less.

Would a dynamic range, when created on a table with a different number of
rows (identical columns though) be copy/paste suitable for the other tables?
Or would each need edited?
 
T

T. Valko

That appears to be working correctly:
=SUMPRODUCT(--(P2:p99999="Refuelling with
JetA"),--(S2:S99999="N208SA"),N2:N99999)
Is there a way to make this all work with
=SUMIF instead of =SUMPRODUCT ?
Click to expand...

If you're referencing 99,999 rows then you must be using Excel 2007.

You can use the SUMIFS function which is much more efficient than
SUMPRODUCT.

=SUMIFS(N:N,P:p,strings!A21,S:S,strings!A44)

This also solves the increasing range problem. You can reference the entire
column and SUMIFS will only calculate the actual used range.

--
Biff
Microsoft Excel MVP


Crashin said:
That appears to be working correctly:
=SUMPRODUCT(--(P2:p99999="Refuelling with
JetA"),--(S2:S99999="N208SA"),N2:N99999)

Is there a way to make this all work with =SUMIF instead of =SUMPRODUCT ?
Using SUMPRODUCT, if the range includes any empty cells, the result is
always #VALUE

I tried another route, using:
=SUM(IF(($P2:$P24="Refuelling with JetA")*($S2:$S24="N208SA"),N2:N24,""))
but with that I was getting #N/A or #VALUE when the range included any
empty
cells, forcing me to update the formula each time the number of rows
increases (far too often to be reasonable going through and changing the
formula).
The P99999 seems to be working properly with SUMPRODUCT at this point, but
I'd had similar issues in the past (may have been my own error).

Thanks for the idea of having static cells with the conditional criteria.
I've made a new sheet simply for that purpose, wasn't too tough to change
formulae to reference the sheet/cell needed.

As it stands now:
=SUMPRODUCT(--(P2:p99999=strings!A21),--(S2:S99999=strings!A44),N2:N99999)
is providing accurate results, and is the base formula for which changes
are
being derived from.

Looks to be a point where I can get a lot more done for now, thanks a ton!

[original left-overs after edit]

Sorry to change variables in the middle there... There's basically one
formula that will work for all of them, just taking out the parts I don't
need.

What I'm looking for with the range question, is how to build the formula
correctly, and not have to go back in and edit it each time the number of
rows increases (very very frequent) - That may work with a dynamic range,
but
that's secondary to getting the formula to pull the right information,
nothing more, nothing less.

Would a dynamic range, when created on a table with a different number of
rows (identical columns though) be copy/paste suitable for the other
tables?
Or would each need edited?


T. Valko said:
Try something like this for multiple conditions.

I like to use cells to hold the criteria.

A1 = Group payment
B1 = SoCal Airways

=SUMPRODUCT(--(P2:p100=A1),--(L1:L100=B1),N1:N100)

As far as the size of the data range increasing over time, just increase
the
size of the referenced ranges:

=SUMPRODUCT(--(P2:p1000=A1),--(L1:L1000=B1),N1:N1000)

Or, use dynamic ranges:

http://contextures.com/xlNames01.html#Dynamic
Click to expand...
Click to expand...
 
C

Crashin

Yes, using '07, I've got rows 1-1048576

Hahah... SUMIFS would be more efficient, huh? That's what I thought
comparing the attempts with nested =SUM(IF(...)) to =SUMPRODUCT

I'll have to try it out another night, too many numbers, can't see straight
anymore. =)
That, and the .xml feed isn't updating properly right now... endless
"hourglass" with Excel when trying on the BIG page.

Thanks again for all the help thus far. It's certainly helped me get 5
steps closer to where I need the workbook to be, functionality-wise.


:
 

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

Hlookup 0
SUM formula after HLOOKUP result true 9
Hlookup with Merged cell as look up value 1
hlookup function dilemma 5
Count Number of Unique Cells That Don't Have Certain Characters 0
Use TODAY() function as the criteria in a SUMIF formula 1
Offsetting and HLOOKUP 5
Copy addition formula of non-consecutive horizontal cells 2

Top