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
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[TD]K
[/TD]
[TD]L
[/TD]
[TD]M
[/TD]
[TD]N
[/TD]
[TD]O
[/TD]
[/TR]
[TR]
[TD]...
[/TD]
[TD]Bob
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Joe
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]N
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Sal
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]Vernon
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]...
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]N
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]...
[/TD]
[TD]Y
[/TD]
[TD]N
[/TD]
[TD]Y
[/TD]
[TD]N
[/TD]
[TD]N
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]...
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]N
[/TD]
[TD]Y
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]...
[/TD]
[TD]Y
[/TD]
[TD]N
[/TD]
[TD]Y
[/TD]
[TD]N
[/TD]
[TD]Y
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]...
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]...
[/TD]
[TD]Y
[/TD]
[TD]N
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD]...
[/TD]
[TD]Y
[/TD]
[TD]N
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD]...
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD]Y
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD][/TD]
[TD]12
[/TD]
[TD]8
[/TD]
[TD]12
[/TD]
[TD]9
[/TD]
[TD]9
[/TD]
[TD]#N/A
[/TD]
[TD]#N/A<strike></strike>
[/TD]
[TD]#N/A
[/TD]
[TD]#N/A<strike></strike>
[/TD]
[TD]#N/A
[/TD]
[TD]#N/A<strike></strike>
[/TD]
[TD]#N/A
[/TD]
[TD]#N/A<strike></strike>
[/TD]
[/TR]
[TR]
[TD]<strike></strike>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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