Summing/counting data when columns match each other

[kitkat]

I've got Worksheet1 with all my data, though only 4 columns of eac
entry matter. For clarity's sake, I've got a separate worksheet calle
"Summary," which is where I want to put all my commands.

In Worksheet1, column D is the billing company, column F is the bille
company, column H is the the category of thing sold, and column Q i
the price.

In Summary, I'd like to generate a row for every company that did an
billing. Column A: name. Column B: Value of sales to themselves.
Column C: Number of sales to themselves they did. Column D: Value o
total sales. Column E: Number of total sales. Column F: Percen
(B/D*100). Column G: Among the internal sales, how many were from on
subdivision to itself? (Billing subdivision Worksheet1!C, Bille
subdivision Worksheet1!E). Column H: Among the internal sales, ho
many were from one subdivision to another?

*However, there's no reason to answer all those questions--I think
can get on a roll once I get a few questions answered.*

How do I say, "If Worksheet1!D matches Worksheet1!F and Worksheet1!H i
"Service,", sum (or count) those rows' values of Worksheet1!Q" ?

How do I say, "If Worksheet1!D rows match *JOHNDOECORP* an
Worksheet1!H is "Service," sum (or count) those rows' values o
Worksheet1!Q" ?
-i thought i had figured this one out in another project i did, but no
it's not working.-

How do I say, "If Worksheet1!D rows match *JOHNDOECORP* an
Worksheet1!H is "Service," and Worksheet1!F in that row matches it, su
(or count) those rows' values of Worksheet1!Q" ?
-that'd be for manually entering all the company names into summary!a
then pasting a similar formula down the line with my \"johndoecorp\
being the value of summary!a.-

Finally, it'd be really nice to know how to make Summary fill in colum
A on its own and hide all the rows who sent no internal bills

 

[Tony Duarte]

It seems to me that what you need is a conditional sum, the easies way
to do that if you don't know how to use the sumif or sum and if
statements combined is by using the conditional sum wizard. If you
don't have it on your TOOLS menu, look at Tools/ Add ins and activate
the conditional sum wizard... I think that may help you... once you
run it ...it'll write a conditional sum formula for you...if it
doesn't do exactly what you want...you may be able to figure out how
to modify it to get exactly what you need.

Hope it helps,

Cheers,

Tony

 

[Domenic]

How do I say, "If Worksheet1!D matches Worksheet1!F and Worksheet1!

is "Service,", sum (or count) those rows' values of Worksheet1!Q" ?
=SUMPRODUCT((Sheet1!$D$2:$D$10=Sheet1!$F$2:$F$10)*(Sheet1!$H$2:$H$10="Service"))
=SUMPRODUCT((Sheet1!$D$2:$D$10=Sheet1!$F$2:$F$10)*(Sheet1!$H$2:$H$10="Service"),Sheet1!$Q$2:$Q$10)

How do I say, "If Worksheet1!D rows match *JOHNDOECORP* an
Worksheet1!H is "Service," sum (or count) those rows' values o
Worksheet1!Q" ?
=SUMPRODUCT((Sheet1!$D$2:$D$10="JOHNDOECORP")*(Sheet1!$H$2:$H$10="Service"))
=SUMPRODUCT((Sheet1!$D$2:$D$10="JOHNDOECORP")*(Sheet1!$H$2:$H$10="Service"),Sheet1!$Q$2:$Q$10)

How do I say, "If Worksheet1!D rows match *JOHNDOECORP* an
Worksheet1!H is "Service," and Worksheet1!F in that row matches it
sum (or count) those rows' values of Worksheet1!Q" ?

=SUMPRODUCT((Sheet1!$D$2:$D$10="JOHNDOECORP")*(Sheet1!$H$2:$H$10="Service")*(Sheet1!$F$2:$F$10="JOHNDOECORP"))
=SUMPRODUCT((Sheet1!$D$2:$D$10="JOHNDOECORP")*(Sheet1!$H$2:$H$10="Service")*(Sheet1!$F$2:$F$10="JOHNDOECORP"),Sheet1!$Q$2:$Q$10)

Of course, instead of stated specifically within the formula whic
company you're interested in, you can reference the appropriate cell i
Column A. This way, you can stick your formula in the appropriat
column of your summary sheet, copy down, and it will pick up th
company in Column A.

Finally, it'd be really nice to know how to make Summary fill i
column A on its own...

Assuming your data in your Summary sheet starts in Row 2, enter thi
formula in A2 and copy down until you get #N/A:

=INDEX(Sheet1!$D$2:$D$10,MATCH(0,COUNTIF(Summary!$A$1:A1,Sheet1!$D$2:$D$10),0))

entered using CTRL+SHIFT+ENTER.

...and hide all the rows who sent no internal bills. [/B]

You can do this using AutoFilter.

Hope this helps