Adding cells from different spreadhseets

[Iain McBride]

Hi - another query for people more Excel savvy than me......

I am trying to make a cumulative total of people attendance from different spreadsheets onto a master template but discounting the term "#N/A"

So, I am trying to total the numbers from cells C15:AH15

B

C

D

E

F

G

H

I

J

K

L

M

N

O

...

Bob

Y

Y

Y

Y

Y

4

Joe

Y

Y

Y

Y

N

5

Sal

Y

Y

Y

Y

Y

6

Vernon

Y

Y

Y

Y

Y

7

...

Y

Y

Y

Y

N

8

...

Y

N

Y

N

N

9

...

Y

Y

Y

N

Y

10

...

Y

N

Y

N

Y

11

...

Y

Y

Y

Y

Y

12

...

Y

N

Y

Y

Y

13

...

Y

N

Y

Y

Y

14

...

Y

Y

Y

Y

Y

15

12

8

12

9

9

#N/A

#N/A<strike></strike>

#N/A

#N/A<strike></strike>

#N/A

#N/A<strike></strike>

#N/A

#N/A<strike></strike>

<strike></strike>

<tbody>
</tbody>

The auto calculation I have doesn't work but the cells carry #N/A when not completed - is there a way to add all the cells up (but ignoring cells with "#N/A"?

Thanks - sorry for the poor explanation!!!

 

[Joe4]

What is the exact formula that is returning the #N/A?

Note that you should be able to handle it by wrapping it in an IFERROR statement, i.e.
=IFERROR(original formula,0)

 

[Iain McBride]

Hi Joe - the #N/A is created when there is a blank cell attached the following formula:

=SUMPRODUCT(--(MATCH(L3:L14,L3:L14,0)=ROW(L3:L14)-ROW(L3)+1),--(LEFT(L3:L14,1)="G"))+COUNTIF(L3:L14,"1:1")

 

[Joe4]

So, if that formula is returning the #N/A error, you can modify it to return a 0 or empty string instead of the error using the method I proposed.

To return a 0:
=IFERROR(SUMPRODUCT(--(MATCH(L3:L14,L3:L14,0)=ROW(L3:L14)-ROW(L3)+1),--(LEFT(L3:L14,1)="G"))+COUNTIF(L3:L14,"1:1"),0)

To return an empty string:
=IFERROR(SUMPRODUCT(--(MATCH(L3:L14,L3:L14,0)=ROW(L3:L14)-ROW(L3)+1),--(LEFT(L3:L14,1)="G"))+COUNTIF(L3:L14,"1:1"),"")

 

[Iain McBride]

Outstanding!!
Thank you for this!! Sure its so simple for you - but blows my mind!!

 

[Joe4]

You are welcome.

Yes, IFERROR is a very handy-dandy little function for handling errors.
You can read up more on it here: https://www.techonthenet.com/excel/formulas/iferror.php