Sum which ever cells are >500, out of A1, A7, A8

**gameoverman** · 08-03-2020, 05:05 PM

Hi there, anybody suggest a formula?

Sum an array of cells - but not a consistent range. Just discrete cells e.g. A1, A7, A8.

BUT:
- Only add the cell's value to the total sum if the cell's value is a number greater than 500

Seems so easy but l've scoured the web and cannot quite find this situation on any forum.

**FDibbins** · 08-03-2020, 05:18 PM

What is in the inbetween cells?

Will this work?
=SUMIFS(A1:A8,A1:A8,">=500")

**gameoverman** · 08-03-2020, 05:27 PM

Hi there, stuff that cannot be included is in the inbetween cells, hence the title show i don't want a series but discrete cells

**TMS** · 08-03-2020, 06:25 PM

Try:

Formula:

Please Login or Register  to view this content.

**TMS** · 08-03-2020, 07:04 PM

Or, this also works and is a bit shorter:

Formula:

Please Login or Register  to view this content.

**gameoverman** · 08-03-2020, 08:26 PM

Hi there, but surely that would also include A2, A3 etc.?

I only want A1, A7, A8 to be considered,

If any of these are a number, and >500, then l want them added to the tally. (The tally = the sum of A1, A7 and A8 but only if any of them are >500 , as mentioned)

EDIT: Ohhhh i see what you did, you caused specific cells to be disregarded. Okay thanks, l guess that's about as elegant as it gets. Wish these spreadsheets would hurry up and A.I. so that i can have real conversations with them and every once in a while tell them to calc something in plain English!!!!

**gameoverman** · 08-03-2020, 08:38 PM

Ok but what if there are vast distances between the cells i'm interested in?

E.g. what if l am only looking at A1, A45, A67, A91?

**bebo021999** · 08-03-2020, 09:38 PM

With rows are hard coded, like 1,45,67,91 try

Please Login or Register  to view this content.

If more discrete rows, try a helper columns to store them then as my attachment.

**TMS** · 08-03-2020, 09:39 PM

You get answers to the questions you ask. We cannot second guess your requirements.

You could try adding a helper column with 1s and 0s and refer to that instead of the static matrix.

**gameoverman** · 08-03-2020, 11:33 PM

bebo021999

Hi there, it's not working with my LibreOffice calc i'm afraid

I'd like to avoid a helper column too.

**FDibbins** · 08-04-2020, 12:40 AM

to help everyone to stop guessing, read the yellow banner above and upload a sample file please.

Sum which ever cells are >500, out of A1, A7, A8

LinkBack

Thread Tools

Rate This Thread

Display

Sum which ever cells are >500, out of A1, A7, A8

Re: Sum which ever cells are >500, out of A1, A7, A8

Re: Sum which ever cells are >500, out of A1, A7, A8

Re: Sum which ever cells are >500, out of A1, A7, A8

Re: Sum which ever cells are >500, out of A1, A7, A8

Re: Sum which ever cells are >500, out of A1, A7, A8

Re: Sum which ever cells are >500, out of A1, A7, A8

Re: Sum which ever cells are >500, out of A1, A7, A8

Re: Sum which ever cells are >500, out of A1, A7, A8

Re: Sum which ever cells are >500, out of A1, A7, A8

Re: Sum which ever cells are >500, out of A1, A7, A8

Thread Information

Users Browsing this Thread

Similar Threads

Count cells with specific year and add up cells adjacent to the counted cells

Formating Input Cells Blue, Formulated Cells Black, Linked Cells Green

[SOLVED] Linking cells globally to allow users the ability to change cells on separate sheet/cells.

combining many cells in one cells keeping character font and hyperlinks of all cells

[NOT SOLVABLE] Cells Within Cells/ Multidimensional Cells/ Sub Cells/ Embedded Tables

Formula: Drop Down List -> Choose Option Finds Cells & Replace Cells with Cells

Count cells based on the sum of cells and cells other meeting additional criteria

Bookmarks

Bookmarks

Posting Permissions