Perform SUM based off COUNT

[Guest]

I have used the following to get a grand total count:
=IF(COUNTIF('Sheet 1'!E2:E1344,">=0")-COUNTIF('Sheet
1'!E2:E1344,">30"),SUM('Sheet 1'!K:K),"")

I would now like to get a total for everything in column E matching the
above condition. How do I code that into my formula?

 

[PCLIVE]

See if this is what you want:

=SUMPRODUCT(--('Sheet 1'!E2:E1344>=0),--('Sheet 1'!E2:E1344<=30),('Sheet
1'!K2:K1344))

Note: I noticed you have a space between "Sheet" and "1". By default, I
don't think there is a space in the tab names.

HTH,
Paul

 

[Guest]

PERFECT!! Thanks!!

See if this is what you want:

=SUMPRODUCT(--('Sheet 1'!E2:E1344>=0),--('Sheet 1'!E2:E1344<=30),('Sheet
1'!K2:K1344))

Note: I noticed you have a space between "Sheet" and "1". By default, I
don't think there is a space in the tab names.

HTH,
Paul

 

[Bob Phillips]

same principle

=SUMIF('Sheet 1'!E2:E1344,">=0")-SUMIF('Sheet 1'!E2:E1344,">30")

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)